CODEWITHSUNDEEP
databasepostgresqldatabase-design
Ilja KO
Ilja KO

Reputation: 1616

PrImary key or foreign key for a m-to-n relation table

I have a table in my database that models a m-to-n relation. The primary key of this relation is of course the combined primary keys of the 2 entities involved in this relation.

The relation goes like this: m customers have n orders

create table customer(
cid             SERIAL PRIMARY KEY,
...
);


create table order(
oid             SERIAL PRIMARY KEY,
...
);


create table has(
oid             INTEGER REFERENCES order(oid) ON DELETE CASCADE,
cid             INTEGER REFERENCES customer(cid) ON DELETE CASCADE,
FOREIGN KEY (oid,cid) or PRIMARY KEY (oid,cid)
);

I'm a bit confused on what to use here: primary key or foreign key to put them in relation?

thanks in advance for any help.

Jaiel

Upvotes: 1

Views: 579

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521073

You should be using PRIMARY KEY for the composite key in table has. The combination of the order and customer IDs in the has table is a primary key in that table, because it allows the identification of a unique single record.

Note that both oid and cid in table has are foreign keys, pointing to the order and customer tables, respectively.

Upvotes: 1

Related Questions