CODEWITHSUNDEEP
sqldatabase-designforeign-keysforeign-key-relationship
Oscar Foley
Oscar Foley

Reputation: 7025

Is correct have a table in a 1 to 1 relationship with a FK that is also a PK?

I have one table called Player

I want to create another table that has a 1 to 1 relationship to Player named PlayerExtraInfo to contain some columns. So I have several options:

  1. Instead of creating a new table, add PlayerExtraInfo columns to Player table

  2. Create PlayerExtraInfo table and have a FK in Player table.

    Player table

    • PlayerID (PK)
    • More columns
    • PlayerExtraInfoID (FK)

    PlayerExtraInfo table

    • PlayerExtraInfo (PK)
    • More columns

  3. The opposite: PlayerExtraInfo contains a FK to Player table. To get sure that relationship remains 1 to 1 I add a unique constrain.

    Player table

    • PlayerID (PK)
    • More columns

    PlayerExtraInfo table

    • PlayerExtraInfo (PK)
    • More columns
    • PlayerID (FK, Unique)

  4. Similar to option 3 but mixing PK and FK in one. In this case my foreign key becomes also the primary key:

    Player table

    • PlayerID (PK)
    • More columns

    PlayerExtraInfo table

    • PlayerID (PK, FK)
    • More columns

I know that options 1, 2 are correct, but due to performance problems I am option for option 3 or 4. And thinking about these 3 and 4 options some doubts about option 4 assault me: So my questions are:

Upvotes: 0

Views: 244

Answers (2)

Dennis Traub
Dennis Traub

Reputation: 51644

When extending one table with another one it is absolutely fine (and recommended as well) to use the primary key as the foreign key.

Upvotes: 4

Dumitrescu Bogdan
Dumitrescu Bogdan

Reputation: 7267

While options 2,3,4 are correct, I would think that option 1 is the one that will give the best performance. Even when using using clustered indexes, an extra join is an extra join. Also a foreign key will degrade performance in respect to the targeted table (I admit that a large number of foreign keys will degrade it, and not one single).

Except the case where the extended table will have but a fraction of the main table's records, or the case where you would like the 2 tables on different partitions, I cannot think of a reason to have that table split into peaces.

Upvotes: 1

Related Questions