2

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)
)
Adam Wenger
  • 17,100
  • 6
  • 52
  • 63
Fady Kamal
  • 1,302
  • 3
  • 13
  • 22

1 Answers1

2

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)
)
Adam Wenger
  • 17,100
  • 6
  • 52
  • 63
  • oh i tried that before but i didn't figure that i forgot the ',',sorry for asking stupid questions tho,i'll mark your answer thank you – Fady Kamal Nov 12 '11 at 18:04
  • 1
    You do not have to feel stupid for asking questions, it's what the site is for. I'm glad I was able to help. – Adam Wenger Nov 12 '11 at 18:06