mysqldesign-patternsdatabase-designshared-primary-key

Design considerations to using Foreign Key as Primary Key

Are there any general design considerations (good/bad/neutral) for using a foreign key of one table as the primary key in another table?

For example, assume the following tables as part of a film catalogue:

titles
------
 id


episodes
--------
 title_id (PK/FK)

Episodes could obviously be done with both an id and a title_id, where id would be the PK and title_id would be UNIQUE, but since title_id is already unique, and, technically, identifies the episode, would there be anything to consider in just using it as the PK? What about in general? What design considerations can you see to this?

Thanks for your thoughts!

Solution

  • The answer to your question is basically the description of the technique known as "shared primary key". Accordingly, I've replaced the two tags about primary-key and foreign-key with the single tag shared-primary-key.

    Shared primary key is a design where the PK of one table is also an FK that references the PK of another table. As the tag wiki for shared-primary-key indicates, this is useful for one-to-one relationships, whether they are mandatory or optional. These relationships are sometimes called IS-A relationships, as in "an automobile is a vehicle". The relationship between vehicles and autos is also known as a class/subclass or type/subtype relationship.

    Like any design technique, it has its benefits and its costs.

    Edit in reply to comment:

    The biggest benefit to shared primary key is that it enforces the 1-to-1 nature of the relationship. Having this rule enforced in the database is generally more productive than trying to make sure that all the application code follows the rule.

    A secondary benefit is that is makes the join between the two tables simple and fast. It's fast (for some database systems) because of the indexes built to support PKs are used by the optimizer to speed up the join.

    A third benefit is that a third table can reference both of these two tables with the same FK.

    The cost is that there is some programming involved in adding a new entry to both tables. The PK from the primary table has to be copied into the secondary table, and the system typically won't do this for you. Also, the join, while fast, is not free.