Udemy logo

sql primary keyWhen 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:

Title First Name Family Name       D.O.B Email Location
Manager Robert Brown 18:03:1968 [email protected] London
Manager Jacqueline Black 22:06:1981 [email protected] New York
Manager Scott Green 23:02:1978 [email protected] Rome
Manager Mary White 01:09:1980 [email protected] Paris
Manager Robert Red 05:05:1975 [email protected] Florida

 

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:

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.

CompanyID Title First Name Family Name       D.O.B Email Location
101 Manager Robert Brown 18:03:1968 [email protected] London
102 Manager Jacqueline Black 22:06:1981 [email protected] New York
103 Manager Scott Green 23:02:1978 [email protected] Rome
104 Manager Mary White 01:09:1980 [email protected] Paris
105 Manager Robert Red 05:05:1975 [email protected] Florida

 

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:

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.

 

Page Last Updated: May 2014

Top courses in SQL

Microsoft SQL for Beginners
Mubashir Hassan | worldwide 80000+ enrollments
4.6 (54)
New
The SQL Programming Essentials 2021 Immersive Training
SDE OCTOPUS, Ahmed Ibrahim
4.5 (40)
New
The Complete SQL Bootcamp 2021: Go from Zero to Hero
Jose Portilla
4.7 (109,610)
Bestseller
The Ultimate MySQL Bootcamp: Go from SQL Beginner to Expert
Colt Steele, Ian Schoonover
4.6 (58,946)
Bestseller
Master SQL For Data Science
Imtiaz Ahmad
4.7 (8,397)
SQL for Beginners: Learn SQL using MySQL and Database Design
Tim Buchalka's Learn Programming Academy, Jon Avis - SQL Instructor
4.5 (15,704)
Learn SQL +Security(pen) testing from Scratch
Rahul Shetty
4.5 (1,806)
Bestseller

More SQL Courses

SQL students also learn

Empower your team. Lead the industry.

Get a subscription to a library of online courses and digital learning tools for your organization with Udemy for Business.

Request a demo