CODEWITHSUNDEEP
mysqldatabasedatabase-designdatabase-administration
user8950166
user8950166

Reputation:

In M:N database relationship it's better to reference by a composite PK or two foreign keys

I have a database that involves a table called U and a table called P .

table U
  U_Id primary key.

table P
P_Id primary key

There is many to many relationship between the two tables.

I have two ways to create relationship between the two table:

1) create a new table that contains a composite Primary Key (U_Id,P_Id)

2) create a new table that contains references from U table and P table as foreign keys.

(U_id and P_id as foreign keys )

 Third_Table

 U_id FK not null

 P_Id FK  not null

What the better option?

Upvotes: 1

Views: 1265

Answers (3)

nvogel
nvogel

Reputation: 25526

I would expect a composite key consisting of two foreign keys. Example code (untested): 

CREATE TABLE Q
(u_id INT NOT NULL FOREIGN KEY REFERENCES U (u_id),
p_id INT NOT NULL FOREIGN KEY REFERENCES P (u_id),
PRIMARY KEY (u_id, pi_id));

Upvotes: 0

James K. Lowden
James K. Lowden

Reputation: 7837

2) create a new table that contains references from U table and P table as foreign keys.

That you need for referential integrity, else the 3rd table could have values that do not refer to anything. (Hurrah for many-to-nothing rows!)

1) create a new table that contains a composite Primary Key (U_Id,P_Id)

If you don't do that, what will be the primary key to the 3rd table? It will have one, right?

A primary key is one form of unique constraint. If a table has more than one unique constraint, the choice of which one to call the primary key is arbitrary. In your case, though, there's only one.

You need some kind of unique constraint on the 3rd table to prevent duplicate rows. Primary Key (U_Id,P_Id) says than any {U_Id,P_Id} pair is unique, and identifies that relationship. From what you say, that's what you want.

Upvotes: 0

reaanb
reaanb

Reputation: 10066

Options 1 and 2 aren't opposed. Your relationship table will contain U_Id and P_Id as foreign keys, and the combination of these two columns should at least be marked as a unique key, if not a primary key.

Some DB designers prefer to introduce a surrogate identifier as primary key. Others (including myself) prefer to use the combination of foreign keys as the primary key.

Upvotes: 2

Related Questions