0

I have a Table "Position" wich allready exists

  • PositionID (PK) int
  • PositionText
  • ...

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.:

  • PositionID (PK, FK to Position-Table)
  • CommentText

OR

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

  • CommentID (PK)
  • PositionID (FK to Position-Table)
  • CommentText

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?

PassionateDeveloper
  • 14,558
  • 34
  • 107
  • 176
  • 9
    As far as DBAs are concerned, "guaranteed through code" is _exactly_ equivalent to "not guaranteed at all" :-) However, if you make it the primary key of the new table, it _is_ guaranteed. Through the DB. Where it _should_ be. – paxdiablo Jan 12 '15 at 07:40
  • I would go with second option. What if relationship changes in future to accommodate for one to many scenario? – danish Jan 12 '15 at 07:45

2 Answers2

4

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.

wvdz
  • 16,251
  • 4
  • 53
  • 90
0

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.

Community
  • 1
  • 1
Albert
  • 24
  • 2