CODEWITHSUNDEEP
sql
PassionateDeveloper
PassionateDeveloper

Reputation: 15138

Database normalization: Foreign Key = Primary Key?

I have a Table "Position" wich allready exists

Now I have to create a new Table "PositionComment" (I'm not allowed to edit the Table Position, so I have to create a new one). The relation is 1:0..1 (it is garantied through code, that 1 position can only have 1 comment).

So i need to have a primary key in this new table - but:

For the rules of normalisation:

Do I have to make the foreign key = primary key, because it is 1:0..1 e.g.:

OR

Do I have to make a own primary key e.g.:

The difference is, ofc, that in the first suggestion I use a different PK as PK in the second table... But in the second suggestion I have a double Identifier because the CommentID and the PositionID will be unique for 1 row...

How to do this for the rule of normalization and why?

Upvotes: 0

Views: 1708

Answers (2)

wvdz
wvdz

Reputation: 16641

You are absolutely right, to implement a 1-1 relationship you should usually consider merging them into the same table. If you have a good reason not to do that, a good method to enforce the 1-1 relationship is to use the foreign key as a primary or alternate key in the referring table.

Upvotes: 4

Albert
Albert

Reputation: 24

Please take a look at this.

It seems that someone else has asked a similar question before.

Foreign keys are almost always "Allow Duplicates," which would make them unsuitable as a Primary Key.

Instead, find a field that uniquely identifies each record in the table, or add a new field (either an auto-incrementing integer or a GUID) to act as the primary key.

The only exception to this are tables with a one-to-one relationship, where the foreign key and primary key of the linked table are one and the same.

But you should also consider this:

A composite primary key that consists of two foreign keys is also perfectly fine for implementing many-to-many relationships.

Upvotes: 0

Related Questions