SQL Primary Keys and Understanding Good Table Design

When discussing SQL primary keys it is a good starting point to define and explain each of the different types of keys. There are primary, foreign, unique, candidate, natural and surrogate keys. The most logical starting point is to answer the question “What is a key in SQL?”

New to SQL? Become a database table design master at Udemy.com.

According to SQL standards, a key is a subset of columns in a table that allow a row to be uniquely identified. In other words, a column or group of columns that hold a value or set of values that is unique to each row within the boundaries of that table. Therefore, a key can be one or more columns that identify any single row in a table.

However, if a key contains more than the minimum number of keys required to identify a single row, then SQL standards designates that the key is a super key. On the other hand, if a key comprises the minimum number of columns required to identify a single row within the table, then it is a minimal super key.

Consider the table below:

In SQL, a super key is a set of columns that combines values differentiated in a single row from any other row, i.e. the combination will be unique for every row in the table.

Possible super-keys in the table managers are;

1. Family Name,
2. Family Name, D.O.B
3. Family Name, D.O.B, eMail
4. Family Name, D.O.B, eMail, Location

In this small table, it is easy to find unique combinations, but in a table of millions of records, it will be far more difficult to find a unique combination of columns that match just one row.

In the above example, possible minimal keys that uniquely identify a row, are:

• Family Name
• D.O.B
• Email
• Location

The minimum key ‘email’ is the only key likely to scale if the table grows.

Learn more about database schema and design at Udemy.com.

What is a Natural Key?

A natural key is comprised of columns that have a logical relationship with one another within the table. In the above example, Managers, Family Name, D.O.B, Email and Location have a logical relationship. Therefore, any key composing of any of those column(s) would be a natural key as the columns already existed. The database designer did not need to create a column to provide a unique characteristic in the case of a surrogate key.

A surrogate key is an artificial key where the column value has no relationship with the other columns in a table. A surrogate key is usually just a custom column that the designer has added and holds as a value a simple sequential number. The column’s row value increments by 1 as a new row is added. By providing each row in the table with a unique number as an identifier, the designer has created an artificial key for the table. Every row in the table has a unique identifier regardless of changes in policy, coincidence or growth.

Consider the Table Managers after adding a surrogate key.

By adding the surrogate key Company ID, the designer has ensured that the table has a unique minimal key, Company ID, regardless of changes in Email policy or new hires sharing the same family name or D.O.B. By creating a surrogate key, the designer now has a perfect candidate as the primary key for the table with the other candidate key ‘Email’ now becoming a secondary key.

What are the Specific Types of Keys?

In SQL, there are unique keys, contender keys, foreign keys and the most important of all — the primary key. The one thing they all have in common is that they share the same basic principle. They can be constructs of one or more columns. One area where they do differ is in their purpose, especially the foreign key.

The foreign key differs from the other key types. A foreign key is really a pointer to a unique key in another table. A foreign key within a table (A) references a unique key in another table (B), which defines a relationship that links the tables.

A unique key is simply that — a unique key within the table.

There are some basic rules regarding foreign keys and unique keys:

• A table can have multiple unique and foreign keys
• A foreign key must reference a unique key in another table
• A foreign and a unique key can be NULL
• A primary key cannot be NULL
• Each table should have a primary key
• Each table can have only one primary key

It’s very important to select a strong primary key, because it remains unique even during times of change. A primary key uniquely identifies every record in the database and needs to be unique. The database designer selects a primary key from an existing strong stable natural key or more commonly from a custom surrogate key. There can be several candidates for the role of primary key, but there can only be one primary chosen. For this reason, it’s imperative that you carefully choose the right column.

Learn more about SQL and table design at Udemy.com.