USE OF DUPLICATE FOREIGN KEYS WHEN CREATING A TABLE

[murtz]

Hi.. Im a bit stuck on creating a table that references the same foreign key that exists in two different tables.

 

Here's my table layout so far..

 

Student - (student_id, name)

 

Book - (book_id, student_id)

 

Sales - (sale_id, student_id(the seller), student_id(the buyer))

 

Basically.. the student_id in the book table is the 'seller' of the book. When the user logs into the page.. he is the 'buyer' in a sense.. so when that user buys a book.. his ID gets posted into the sales table, whilst the sellers id also gets posted into the SALE table. Both the seller and buyer's ID comes from the Student_id field.

 

Obviously I cant have a duplicate foreign key in the sales table.. so is there any way round this???

[quiettech]

You can't have a duplicate foreign key, correct. But what you are missing is that the Sales table doesn't need to have the fields named exactly as its helper tables.

 

Sales - (sale_id, seller, buyer)

 

Makes sure seller and buyer are of the same type as student_id and then create the foreign key relationship for each of them

 

ALTER TABLE Sales
  ADD CONSTRAINT sales_fk_seller
  FOREIGN KEY (seller)
    REFERENCES Student (student_id)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT; /* check your specific rules */

ALTER TABLE Sales
  ADD CONSTRAINT sales_fk_buyer
  FOREIGN KEY (buyer)
    REFERENCES Student (student_id)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT; /* check your specific rules */