Relationship with foreign keys in SQL?

I am translating a recursive relationship in a schema to sql and i am trying to reference two columns in a table to one column in another table the Schema is as following :

Table: Request
(
   mem_id1,
   mem id2,
   approved
)  

desired foreign keys:

request.mem_id1 will reference member.mem_id  
request.mem_id2 will reference member.mem_id 

I have tried the following:

CREATE TABLE members (
mem_id INTEGER,
f_name VARCHAR(10),
l_name VARCHAR(10),
address VARCHAR(40),
name VARCHAR(20),
domain VARCHAR(15),
PRIMARY KEY(mem_id)
)

CREATE TABLE member_phone (
mem_id INTEGER,
phone_no INTEGER,
PRIMARY KEY (mem_id,phone_no),
FOREIGN KEY (mem_id) REFERENCES members (mem_id)
)

CREATE TABLE request (
mem_id1 INTEGER,
mem_id2 INTEGER,
approved BIT(1),
PRIMARY KEY (mem_id1,mem_id2),
FOREIGN KEY (mem_id1,mem_id2) REFERENCES members (mem_id)
)

Answers


I believe you want two separate foreign keys:

CREATE TABLE request (
mem_id1 INTEGER,
mem_id2 INTEGER,
approved BIT(1),
PRIMARY KEY (mem_id1, mem_id2),
FOREIGN KEY (mem_id1) REFERENCES members (mem_id),
FOREIGN KEY (mem_id2) REFERENCES members (mem_id)
)

Need Your Help

400 Bad request on Spring ajax call

java ajax json spring spring-mvc

I'm getting an error in spring ajax call. I know it's some syntax error but cant find what's it. can someone help?

Comparing SplFileInfo objects always returns true

php

I've pretty much convinced myself that this is just leaky abstraction rearing it's ugly head, but I figured before filing a bug report I might probe Stack Overflow for a more insightful answer...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.