What is an Alternate Key in SQL?

Posted on by Ian

While the primary key is almost certainly the most discussed key in SQL, there are other types of keys that we shouldn’t forget. One such key is the alternate key.

In this article, we’ll look at the concept of the alternate key in SQL.

Quick Intro to Keys in SQL

In SQL, a key is an attribute (column) or a set of attributes that uniquely identifies a tuple (row) in a table. Keys are essential for the following reasons:

  • Uniqueness: Ensuring that no two rows have the same key value.
  • Data Integrity: Maintaining the accuracy and consistency of data.
  • Relationships: Establishing connections between tables (e.g., foreign keys).

There are various types of keys. Common types of keys in SQL include:

Some of these are enforced with a specific constraint that defines them as that key type (e.g., primary key, foreign key). Others are not necessarily enforced with a specific constraint, but are more of an abstract concept (although they can also be enforced with a constraint of some type). These key types consist of one or more columns with certain characteristics that qualify it as the particular key.

Some keys can be more than one type. For example, we could have a primary key that’s made up of multiple columns, making it a composite key. A primary key is also a candidate key; it was a candidate key that was chosen as the primary key.

What is an Alternate Key?

An alternate key (also known as secondary key) is a column or a combination of columns in a table that can uniquely identify each row, but is not the primary key. In other words, it is an alternative to the primary key that could have been chosen as the primary key but wasn’t.

So an alternate key is also a candidate key – one that wasn’t chosen as the primary key.

There can be only one primary key for a table, and so alternate keys are the candidate keys which are not the primary key.

Characteristics of an Alternate Key

So, an alternate key possesses the following characteristics:

  • Uniqueness: Like a primary key, an alternate key must have unique values for each row.
  • Non-Null: The columns that form an alternate key cannot contain NULL values.
  • Candidate Key: An alternate key is essentially a candidate key that was not selected as the primary key.

Example

Consider a table Employees that contains the following columns:

  • EmployeeID (primary key)
  • SocialSecurityNumber (SSN)
  • EmailAddress
  • PhoneNumber

In this table, both SocialSecurityNumber and EmailAddress could serve as unique identifiers for each employee, as long as their values are unique and cannot be NULL. However, since EmployeeID is chosen as the primary key, SocialSecurityNumber and EmailAddress are alternate keys.

Alternate Key vs. Primary Key

The primary key is the main key chosen to uniquely identify rows within a table. It is the most critical key because it is used in relationships with other tables (through foreign keys) and often serves as the primary means of accessing records.

Differences between Alternate Key and Primary Key

  • Selection: The primary key is chosen from the set of candidate keys. Alternate keys are the remaining candidate keys not chosen as the primary key.
  • Purpose: The primary key is the main identifier for rows. Alternate keys serve as secondary identifiers and can be used to enforce uniqueness on other columns.
  • Usage in Relationships: The primary key is typically used in defining relationships (e.g., foreign keys) with other tables, while alternate keys generally are not. That’s not to say that they couldn’t be.

How to Define an Alternate Key in SQL

An alternate key is already an alternate key by possessing the characteristics of an alternate key. However, we can also go a step further and create a constraint that enforces its uniqueness.

We can do this with the UNIQUE constraint. The UNIQUE constraint ensures that all values in a column or a group of columns are distinct, which is the primary requirement for an alternate key.

Example

Here’s an example of creating UNIQUE constraints against two columns when creating a table:

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    SocialSecurityNumber CHAR(11) UNIQUE,
    EmailAddress VARCHAR(255) UNIQUE,
    PhoneNumber VARCHAR(15)
);

In this example:

  • EmployeeID is the primary key.
  • SocialSecurityNumber and EmailAddress are alternate keys, and we have applied a UNIQUE constraint against each of them.

Adding a UNIQUE Constraint to an Existing Table

If you want to enforce the uniqueness of an existing alternate key, you can add the UNIQUE constraint with the ALTER TABLE statement:

ALTER TABLE Employees
ADD CONSTRAINT AK_SocialSecurityNumber UNIQUE (SocialSecurityNumber);

Here, we’ve provided a name of AK_SocialSecurityNumber for the constraint. We can use this name if we ever need to modify it or drop it.

Practical Use Cases of Alternate Keys

While the alternate key might be more of an abstract concept than a technical implementation, it can still play an important role in database operations, especially when enforced with a UNIQUE constraint.

Here are some areas where they can help:

Data Integrity

Alternate keys, when enforced with a UNIQUE constraint, can help maintain data integrity by ensuring that certain columns, besides the primary key, maintain unique values across the table. This is particularly useful in scenarios where multiple unique identifiers are needed.

Search and Query Optimization

In some cases, querying the database based on the alternate key might be more efficient than using the primary key. For example, searching for a student by EnrollmentNumber might be faster and more user-friendly than using StudentID.

Business Requirements

Certain business requirements may dictate the need for multiple unique identifiers. For instance, a company might require that both an employee’s EmployeeID and SocialSecurityNumber be unique, even though only one serves as the primary key.

Conclusion

Alternate keys can play their part in maintaining data integrity and optimizing search queries.

When designing your next database, consider all potential candidate keys, and strategically choose your primary key while utilizing alternate keys to enforce additional uniqueness where needed. This approach will lead to a more effective and reliable database schema.