SQL - Alternate Key

SQL - Alternate Key

Understanding Keys in a Database

To understand Alternate Keys, we must first understand the hierarchy of keys in relational database theory:

• Candidate Key: A column, or a set of columns, that can uniquely identify every row in a table. A table can have multiple candidate keys. By definition, a candidate key must not contain NULL values.
• Primary Key: The one candidate key that is chosen by the database designer to be the main identifier for the table. It is the most important key for identifying rows.
• Alternate Key: Any candidate key that was not chosen to be the primary key. These are secondary unique identifiers for the table.

What is an Alternate Key?

An Alternate Key is a column or group of columns that provides an alternative way to uniquely identify a row in a table. Think of it as a runner-up in the election for Primary Key.

For example, consider an Employees table. You might have several columns that could uniquely identify an employee:

• EmployeeID (an auto-incrementing number)
• SSN (Social Security Number)
• Username (the company login name)

All three of these are Candidate Keys. If you choose EmployeeID as the Primary Key, then SSN and Username become the Alternate Keys.

There is no special ALTERNATE KEY syntax in SQL. An alternate key is a conceptual term. You implement it by enforcing its uniqueness and non-nullability with constraints.

How to Implement an Alternate Key

To properly implement an alternate key and ensure it can reliably identify a row, you must enforce two properties using SQL constraints:

1. Uniqueness: Use a UNIQUE constraint to ensure no two rows have the same value in that key.
2. Non-Nullability: Use a NOT NULL constraint to ensure the key always has a value.

A column (or set of columns) with both a UNIQUE and NOT NULL constraint is, for all practical purposes, an alternate key.

Foreign Keys and Alternate Keys

A powerful and often overlooked feature of SQL is that a FOREIGN KEY can reference any UNIQUE key in another table, not just the primary key. This means you can create a foreign key relationship based on an alternate key, which is extremely useful when integrating with systems that use a different natural identifier.

Example: Defining and Using Alternate Keys

Let’s create a Users table where UserID is the primary key, and both Email and Username are alternate keys. Note the use of PRIMARY KEY, UNIQUE, and NOT NULL.

CREATE TABLE Users (
    UserID INT GENERATED ALWAYS AS IDENTITY, -- Modern auto-increment syntax for PostgreSQL
    Username VARCHAR(50) NOT NULL,
    Email VARCHAR(100) NOT NULL,
    FullName VARCHAR(100),

    -- 1. Define the Primary Key
    CONSTRAINT PK_Users PRIMARY KEY (UserID),

    -- 2. Define the Alternate Keys
    CONSTRAINT UQ_Users_Username UNIQUE (Username),
    CONSTRAINT UQ_Users_Email UNIQUE (Email)
);

-- Note: In MySQL, you might use 'INT AUTO_INCREMENT PRIMARY KEY' for the UserID.

In this schema:

• Candidate Keys: (UserID), (Username), (Email)
• Primary Key: (UserID)
• Alternate Keys: (Username), (Email)

Now, let’s create a Posts table that references the Users table by its alternate key, Username.

CREATE TABLE Posts (
    PostID INT PRIMARY KEY,
    AuthorUsername VARCHAR(50) NOT NULL,
    Title VARCHAR(200),
    Content TEXT,

    -- This foreign key points to an ALTERNATE KEY in the Users table
    CONSTRAINT FK_Posts_Users FOREIGN KEY (AuthorUsername) REFERENCES Users (Username)
);

Primary vs. Alternate Keys: The Choice Matters

When you have multiple candidate keys, how do you choose the primary key? This is a fundamental database design decision.

Surrogate Keys (e.g., UserID)

A surrogate key is an artificial key with no business meaning, typically an auto-incrementing integer. This is the most common and often best practice approach.

• Pros: Stable (never changes), small (efficient for joins), simple.
• Cons: Has no real-world meaning.

Natural Keys (e.g., Email, SSN)

A natural key is a candidate key that is also a real-world attribute.

• Pros: Has business meaning, can save you a JOIN in some queries.
• Cons: Can change (e.g., a user changes their email), can be long (inefficient for joins), can have privacy implications (e.g., SSN).

For these reasons, a surrogate primary key is generally recommended, while natural keys are enforced as UNIQUE NOT NULL alternate keys.