Translated from 日本語 • View original In a separate article, I was researching "surrogate pairs" in Unicode, and an article about "surrogate keys" and "compound primary keys" in databases was published in Surrogate I got caught in a connection and derailed as much as I could (laughs)
If you are not very familiar with databases, what are "surrogate keys", "compound primary keys", and "natural keys"? ──There must be many people who will be, so I thought I'd summarize it as a review of myself.
Primary Key (Primary Key)
Key constraints that exist in most tables in a database to uniquely manage data. Duplicate values cannot be placed in the column where this key is specified (unique key constraints), nor can they be blank (NULL).
Unique Keys (Unique Keys, Unique Indexes)
A key constraint that cannot store duplicate values in the column where this key is specified, and must be unique. However, unlike the primary key, whitespace (NULL) can be stored, and only whitespace (NULL) can be duplicated. This key is also indexed at the same time as it is specified, so it is synonymous with a unique index.
Surrogate Keys (Surrogate Keys)
A primary key when a primary key constraint is applied to a column with a unique value that stores a sequence number by autoincrement attributes, etc. It is also called a surrogate key because it replaces the set of columns (natural keys) used to identify the unique row records in a table. It is systematically beneficial because it can identify the uniqueness of all row records with a single column, but it is meaningless as data used for actual services, etc., and from the perspective of data users, it is only a column that unnecessarily squeezes the physical storage area of the database.
Natural Keys (Natural Keys)
A set of columns with a unique key constraint to identify row records in a table. For example, in WordPress, if the combination of "post ID" and "tag ID" can uniquely identify post data belonging to a certain tag, the aggregate of those two columns is called a natural key, and the table with that natural key is 'wp_term_relationships'. In principle, the columns contained in the natural key must have a unique key constraint, so they are often set to the primary key. From the perspective of data users, natural key-type tables, whose uniqueness is identified only by meaningful data, have clear uses and are not wasteful.
Composite Primary Keys
Tables such as multiple primary keys, surrogate keys + natural keys, and only natural keys in multiple column sets without primary keys can be said to be compound primary key tables. In short, it is a type of table where the uniqueness of a row record cannot be identified with only a single column. This is a common type of relationship table that only manages foreign key joins to make other tables relate to each other (e.g., the 'wp_term_relationships' table in WordPress, which we mentioned in the Natural Keys section). Those who have looked into relationship tables or DB design should understand it well, but such compound primary key tables are structured to centrally manage the surrogate keys of each external table, and the relational relationships of real data are often not clear at a glance. On the other hand, if the primary key of a table with the natural key as the primary key is related as a foreign key, the data combinability can be seen at a glance just by looking at the relationship table, but string data with high reference costs will exist in the database, which will lead to a decrease in performance of the database as a whole. The pressure on the physical storage area also goes beyond the surrogate key. In addition, especially for tables where natural keys are involved in uniqueness identification, the system cost of handling unique data is high.
As I was sorting out the words, I felt again that it seems quite troublesome to operate the table with a compound primary key. Well, it's actually troublesome. For example, if you want to change the value of one of the natural keys of a compound primary key, you have to put the same natural key in the search criteria to identify the row record, which is a bit annoying to think about processing. If you try to UPDATE a table with two or more primary keys, MySQL will get angry...
My personal conclusion is that it is better to make the database easy for the system to respond to, not to people, and I think it is putting the cart before the horse to insist on human data visibility at the expense of performance. I think that the correct form of the system that uses the database is to create and shape the data that is easy for humans to recognize. This is because in most cases, the data requested by humans cannot be useful unless it is filtered by time, case, and person... In other words, I think it is best practice to use a surrogate key instead of a composite primary key for the primary key.