Описание тега shared-primary-key

One-to-one relationships are typically of the IS-A variety of relationships. IS-A relationships are known in object modeling as class/subclass designs. IS-A relationships are known in ER modeling as generalization/specialization designs.

Shared primary key comes with a cost. In general, DBMS products support part but not all of the shared primary key concept. In the main table, the primary key is just declared in the usual way. In any secondary tables, the key is declared both as a primary key and as a foreign key, referencing the primary key in the main table. This is enough to enforce the one-to-one relationship.

When new entries are to be made in the main table and in one or more secondary tables, the program doing the insert has to insert the row into the main table first, and then propagate the value of the main table's primary key to the secondary table(s). This propagation can be thought of as "poor man's inheritance".

Note that the entry in the secondary table is optional, while the entry in the main table is mandatory in order to maintain referential integrity. Also note that you shouldn't use the autonumber feature of the DBMS in the primary keys in the secondary table.

Here are the nice features you get from using a shared primary key. Most DBMS products will create indexes on all columns (fields) that are declared as primary keys. And most DBMS products will use a very fast algorithm when performing joins where there is an index on both sides of the join condition. Also, a join between the main table and a secondary table will automatically drop out entries in the main table that do not pertain to the given secondary table.

A foreign key used in some third table that references the main table will automatically reference the related rows in relevant secondary tables. This makes the resulting data structure more flexible.

Shared Primary Key is very often used in conjunction with a design pattern described under the tag class-table-inheritance. The relationship between classes and subclasses is an IS-A variety of relationship.