Probably the most widely known key type in SQL is the primary key, which is chosen to uniquely identify each row in a table. Perhaps next is the foreign key, which is used to establish a relationship between tables.

But there are more key types than this, and the differences between them can be subtle, but important. Here we’ll look at nine of the various key types in SQL.

Primary Key: The Main Identifier

The Primary Key is a column or a set of columns that uniquely identifies each record within a table. The primary key ensures that no two rows have the same identifier, and it cannot contain NULL values. Each table in a database can have only one primary key, making it the main reference point for identifying records.

Example:
If you have a table named Customers, the CustomerID column could serve as the primary key, ensuring that each customer is uniquely identifiable by their ID.

Foreign Key: Linking Tables Together

The Foreign Key is used to establish relationships between two tables. It’s a column or a set of columns in one table that refers to the primary key or a unique key in another table. This relationship enforces referential integrity, ensuring that the data between the tables remains consistent.

Example:

In the example in the above image, the CityId column of the Customer table is a foreign key that references the CityId primary key in the City table. A foreign key constraint dictates that any value entered into Customer.CityId must already be present in City.CityId. If not an error occurs, and the value is not inserted into the table. This ensures that there’s no orphaned records in the Customer.CityId column.

Unique Key: Ensuring Uniqueness in Data

A Unique Key is a column or set of columns that uniquely identify a row in a table. To enforce the uniqueness, we can create a UNIQUE constraint or UNIQUE index. This ensures that all values in a column or a set of columns are unique across the table. While similar to a primary key, a unique key differs in that a table can have multiple unique keys, and these keys can contain NULL values (though only one NULL per column is allowed in many RDBMSs).

Example:
In a Users table, the Email column might be a unique key with a UNIQUE constraint to ensure that no two users have the same email address. But this column won’t necessarily be the primary key. Perhaps this table has a UserId column for the primary key.

Candidate Key: Potential Primary Keys

A Candidate Key is a column or a set of columns that could serve as a primary key. A table can have multiple candidate keys, but only one is chosen as the primary key. The remaining candidate keys can be used as alternate keys. Candidate keys are also known as minimal superkeys, because no subset of the candidate key’s columns can uniquely identify each row.

Example:
In a Books table, both ISBN and BookID might qualify as candidate keys, but only one can be selected as the primary key.

Super Key: The Set of Unique Identifiers

A Super Key (or Superkey), is a combination of columns that can uniquely identify a record in a table. This set includes candidate keys, primary keys, and may contain additional columns that are not necessary for uniqueness.

Example:
In a Products table, a super key could be a combination of ProductID and ProductName, even though ProductID alone might be sufficient to identify a record.

Composite Key: Combining Columns for Uniqueness

A Composite Key is formed by combining two or more columns to create a unique identifier for a record. This type of key is used when no single column can uniquely identify a record.

Example:
In an Enrollments table, a composite key could consist of both StudentID and CourseID, ensuring that each combination of student and course is unique.

Alternate Key: The Non-Primary Candidate

An Alternate Key is any candidate key that is not selected as the primary key. It can still be used as a unique key to enforce data integrity within the table.

Example:
If ISBN is chosen as the primary key in a Books table, BookID might serve as an alternate key.

Surrogate Key: The Artificial Identifier

A Surrogate Key is an artificial key used to uniquely identify a record when no natural primary key exists. This is typically an auto-incremented number that has no business meaning outside the database.

Example:
In a Transactions table, an TransactionID could be a surrogate key, automatically incremented with each new transaction.

Natural Key: The Real-World Identifier

A Natural Key is formed from real-world data that naturally exists within the dataset. Unlike surrogate keys, natural keys have business meaning and are derived from the data itself.

Example:
In an Employees table, a combination of SocialSecurityNumber and BirthDate might serve as a natural key.