64  Primary Key

In relational databases, primary keys identify the records in a table as unique. The benefit of finding a record as unique is that querying and updating this record is much faster with this key. Similarly, relationships between tables are maintained by foreign keys that are linked to primary keys.

For example, suppose we have PERSON and PERSON_TRAINING tables. Which trainings a person has received is tracked using the Person primary key in the PERSON_TRAINING table. Therefore, primary key selection is very important in database design.

There are 2 different ideas in primary key selection: Natural Key and Synthetic Key.

Before explaining the differences, pros and cons between the two, let me state that I am in favor of synthetic keys.

64.1 Natural Key

Natural Key proponents recommend choosing a primary key based on information that already exists in the domain when designing a database. Since users are already familiar with this information, natural keys will be much easier to use and adopt. These keys are also easier to use by people who are not familiar with database logic.

In table Table 64.1, license plateCode is selected as the primary key.

Table 64.1: Natural Key Example 1 - City Table
CITY
PlateCode PK
CityName

The advantage of Natural Keys is that they are already known as I mentioned before. As a disadvantage, since these keys have meaning in the domain, our database schema has become domain dependent. Keys need to be changed in requirement changes. For example, let’s assume that TcIdentityNo is selected as the primary key in Table Table 64.2. If this key is entered incorrectly in the first entry, it will be necessary to change this information entered as Foreign Key in all tables connected to this key, for example (PERSON_TRAINING, PERSON_EXPERIENCE).

Table 64.2: Natural Key Example 2 - Person Table
PERSON
TcIdentityNo PK
FirstName
LastName

In table Table 64.3, a somewhat extreme example is the Address table. It is difficult to find a suitable natural key in the address table, so all the information entered had to be made as a composite key.

Table 64.3: Natural Key Example 3 - Address Table
ADDRESS
AddressLine1 PK
AddressLine1 PK
ApartmentName PK
ApartmentNo PK
PostalCode PK
County PK
CityNo PK
CountryNo PK

64.2 Surrogate Key

Instead of selecting a Natural Key, automatically generated keys can be used as primary keys. The advantage is that if the natural key changes, the records in the database are not affected by this situation. Another advantage is that even in case of lack of information in the initial data entries, data can be recorded with the existing information. Others are the convenience in back-end programming and queries.

In the example given earlier with PERSON and TcIdentityNo in the natural key, if TcIdentityNo changes will only affect the PERSON table. In this case, de-coupling will be provided between the domain and the application.

But the most effective argument for the synthetic key is the convenience it brings to programmers. It makes it easier to write code with the appropriate strategy.

The disadvantage of synthetic keys is that they are often less understandable. This is especially true for universally unique identifiers (UUID/GUID).

In table Table 64.4, KisiKey is selected as synthetic key. In this table, duplicate entries of TcKimlikNo can be prevented by adding a unique index on TcKimlikNo

Table 64.4: Synthetic Key Example - Person Table
PERSON
PersonKey PK
TcIdentityNo
FirstName
LastName

One of the important points to note about synthetic keys is that they should not be used to add business meaning. For example, the company numbers given to companies by chambers of commerce are usually a synthetic key generated by databases. But in the below example, they are given a business meaning.

An example of this can be seen in the figure Figure 64.1. This example is taken from an old version of chamber of commerce software. How true or false this situation is is a matter of debate.

Figure 64.1

The Synthetic Key vs. Natural Key debate has turned into a religious debate in some places. Remember that there is never one right way to design a database. It is your application. Choose the most appropriate strategy according to your requirements.

64.3 Auto number (identity)

The synthetic key can be generated on the database side or on the application side that accesses the database. The most preferred form of synthetic key on the database side is automatic number generation. This Auto Number strategy is supported by many databases:

  • Sqlite
  • SQL Server
  • Oracle 12c+
  • MySQL
  • IBM DB2

Auto Number columns are bound to the database table and each time an Insert clause is executed, the next automatically generate the value.

64.4 Sequences

If auto number generated by database is used in more than one table, sequences will be more useful. Sequences are database objects from which users may generate unique integers.