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

row count on dataset different than row count on datagridview

c# winforms datagridview

I'm pretty certain this is a problem with my data binding, but I'm not for sure what the problem is exactly. Using mysql I'm getting rows showing up in my dataset, but no rows showing up in my