CODEWITHSUNDEEP
sqlpostgresql
ScottyBlades
ScottyBlades

Reputation: 13983

How to make a foreign key to a table used for many to many relationships in PostgreSQL?

Let's say I want to create a database for a gift giving event. A person can give many gifts from different people and a person can receive many gifts from people. Lets say I wanted to track comments on gift giving, how would I reference the GiftGiving table in the comments table?

CREATE TABLE People (person_id INT PRIMARY KEY);

CREATE TABLE GiftGiving (
  from_person_id INT,
  to_person_id INT,
  PRIMARY KEY (from_person_id, to_person_id),
  FOREIGN KEY (from_person_id) REFERENCES People(person_id),
  FOREIGN KEY (to_person_id)   REFERENCES People(person_id)
);

CREATE TABLE Comments (
    comment_id SERIAL PRIMARY KEY,
    commentator INT,
    time_stamp TIMESTAMP,
    text TEXT,
    FOREIGN KEY (commentator) REFERENCES People(person_id),
    -- HOW DO I WRITE A FOREIGN KEY TO REFERENCE GiftGiving being commented on?
    -- like this?: FOREIGN KEY (from_person_id, to_person_id) REFERENCES GiftGiving(???)
);

Upvotes: 0

Views: 44

Answers (1)

fog
fog

Reputation: 3391

Almost exactly as you wrote it:

...
from_person_id INT,
to_person_id INT,
FOREIGN KEY (from_person_id, to_person_id) REFERENCES GiftGiving(from_person_id, to_person_id)
...

Upvotes: 1

Related Questions