What Makes Good Primary Key?

The following question was recently asked on Quora:

How do I choose the primary key when there are two unique identifiers?

I disagree that choosing an integer over a character field is the best answer for a Primary Key (PK). I am providing a synopsis of the standards I defined in my Data Architecture Handbook and Data Management Framework. There is additional detail and diagrams under the section Data Design Considerations below.

Data Type

When I first started my 35-year+ career as a data architect, it was true that integer fields were more performant than character.  This has not been true for a while yet the belief still persists.

When I was building a major (multi-terabyte) Enterprise Data Warehouse (EDW) for a law enforcement system, I actually had to prove this to my DBAs via an extensive test and the difference was nano-seconds. What was also proved is that fixed length character is superior to variable-length character.  Names and descriptions are not good candidates for a PK for many reasons.

Key Length

Length not data type has an impact on index performance.

Composite Keys

However, my DBs and EDWs often use composite PKs consisting of multiple columns and there is an insignificant impact on performance. There is also the extra benefit of being able to select by the highest order columns without having to know the exact value you are looking for in the lower level columns; e.g. find all country-subdivisions (state/province/canton…) for a selected country.

Using a Composite Key is far better than using Junction Tables (see Figure 2 below) in between every two related parent and child tables. Increasing the number of tables joined in queries has a definite performance hit and SQL coding complexity.

Two Unique Identifiers

So back to the original question of which column to choose.  Generally, having two columns that uniquely identify each row is against Third Normal Form (3NF) Rules. 3NF is the basis on which relational databases were originally designed by their founder, Codd.

First you need to identify is what is the origin and purpose of each column.  Why was a second unique key added to the table?  I will give you one example I have come across a lot.  Many governmental departments used to create a table of Countries identified by an arbitrary integer number.  At a later date they added the ISO two-character Country Code.  In this case, the two-character code is superior to the four or eight-byte integer. But not for the reason of length.

Reference Code Tables

The point of ISO and other international standards is to provide a set of code values that are independent of language and can be exchanged between all countries and companies.  Therefore, it is the ISO, IATA, ITU, United Nations, Microsoft… code that MUST be the primary key and must be exchanged between organizations.  Therefore, all Reference Code Tables should use the code values defined by the international or industry-specific standards (for instance, Microsoft defined the Language codes).

Operational Data Tables

This is where a judgement call needs to be made.  Look at the purpose of each column and choose the one that makes the most sense for your business.  So, for a Purchase Order table one would want to use the PO Number as defined by the business.  Generally, these keys are integer but not always.  For instance, the General Ledger usually looks like a number but is actually defined as character by most financial accounting systems.

Data Design Considerations

Primary Key Choices

Natural Key

A natural key is formed from attributes that already exist in the real world.^[2]  Especially if it is alphabetic, it can indicate the meaning of the data held in a row.  An example in the figure below is Country, where a mnemonic acronym defined by ISO is used to identify each country.  The two-character ISO Country is pervasive as it is used by the banking, travel, financial, foreign exchange (prefix for the ISO Currency Code), and internet industries (used in web domains).

While some of these attributes will be natural keys, many are auto-incrementing integer numbers such as Purchase Order Number, Transaction Id, Contract Id, Invoice Number, and Customer Id.

Surrogate Key

Surrogate Key is a unique identifier for an object or row in a table and is frequently a sequential auto-number.  It has no relationship to the real-world meaning of the data held in a row.  Surrogate Key is essential for EDWs, which integrate data into a table from multiple data sources.  One cannot use the key defined in the source system as it may conflict, be the same as a key in another system and/or may be changed by the source system.

Data Modelling

If a database has been designed properly there is no problem changing a primary key. The problem is that many developers with limited data architecture experience are still designing databases.  In many instances, databases are not developed with industry-strength Data Modelling (DM) tools.  If a DM tool is used, Referential Integrity business rules will be built right into the database. There will be no need to do your own “coding”.  The RI rules will take care of changes or deletions, and when they are allowed.

Composite Keys

When a PK consists of two or more columns it is called a composite primary key. A composite key is required when one table is dependent on another table. For instance, Country Region (province, state, canton, département…) is only unique within (i.e. dependent on) Country. As shown in the figure below, the Country Region Codes table has a primary key consisting of Country and Ctry Region.

Figure 1: Composite Primary Key

Junction Tables

This following figure is an example of introducing an extraneous table into the model/database.[1]  It provides no value-add and makes queries more complex and less performant due to the extra joins.  Junction tables lead to table proliferation.

Figure 2: Junction Table between every Parent & Child Table

Identifying or Dependent Relationship

Instead directly link the tables using an identifying or dependent relationship; in this case, phones belong to or are used by an entity.  A real plus is that a query can be made that finds all phones used by an entity.  You don’t have to know the phone numbers used by the entity.  This would be a single SELECT statement against a single table.

Figure 3: Child Table with Identifying Relationship

SQL Coding

Poorly coded SQL has probably the most significant impact on query performance.