MySQL - Alternate Key

MySQL - Keys: Primary, Alternate, and Foreign

In relational databases, keys are crucial for uniquely identifying records and establishing relationships between tables. They are the foundation of data integrity and efficient data retrieval. Understanding the different types of keys—Candidate, Primary, Alternate, and Foreign—is essential for designing robust and reliable database schemas.

The Candidate Key: The Pool of Uniqueness

A Candidate Key is a column, or a set of columns, in a table that can uniquely identify each row. A table can have multiple Candidate Keys. For a key to be a candidate, it must have two properties:

• Uniqueness: It must contain unique values for each row.
• Irreducibility: If the key is composite (multiple columns), no subset of its columns can uniquely identify a row.

Think of Candidate Keys as all the potential candidates for being the main identifier of a table.

The Primary Key: The Chosen One

A Primary Key is the Candidate Key that is selected by the database designer to be the main, official identifier for a table. It has a few strict rules:

• It must uniquely identify each record in the table.
• It cannot contain NULL values.
• A table can have only one Primary Key.

In MySQL, you define it using the PRIMARY KEY constraint. It automatically creates a clustered index, which physically sorts the table data, making lookups by this key very fast.

The Alternate Key: The Runner-Up

An Alternate Key is a Candidate Key that was not chosen to be the Primary Key. While not the primary identifier, it still uniquely identifies each row. In practical terms, Alternate Keys are implemented in MySQL using a UNIQUE constraint.

Features of an Alternate Key (enforced by UNIQUE constraint):

• It ensures all values in the column (or combination of columns) are unique.
• A table can have multiple Alternate Keys (multiple UNIQUE constraints).
• Unlike a Primary Key, a UNIQUE constraint can allow one NULL value by default.

The Foreign Key: The Link Between Tables

A Foreign Key is a column (or set of columns) in one table that refers to the Primary Key of another table. It’s the mechanism that links tables together, enforcing referential integrity. This means you cannot add a record to the child table (with the Foreign Key) unless a corresponding record exists in the parent table (with the Primary Key).

Foreign Keys can also define actions for ON UPDATE and ON DELETE events, such as CASCADE (if the parent row is deleted, delete the child rows too) or SET NULL (set the foreign key to NULL).

Visualizing the Keys

                  +-----------------------+
                  |    CANDIDATE KEYS     |
                  | (All unique columns)  |
                  |                       |
                  |  +-----------------+  |
                  |  |  PRIMARY KEY    |  |  (e.g., employee_id)
                  |  +-----------------+  |
                  |                       |
                  |  +-----------------+  |
                  |  | ALTERNATE KEY 1 |  |  (e.g., email)
                  |  +-----------------+  |
                  |                       |
                  |  +-----------------+  |
                  |  | ALTERNATE KEY 2 |  |  (e.g., ssn)
                  |  +-----------------+  |
                  |                       |
                  +-----------------------+

Practical Example: Designing an Employees Table

Let’s design a table for employees. We identify several potential Candidate Keys: employee_id, email, and ssn (Social Security Number). All are unique.

• We choose employee_id as the Primary Key. It’s a numeric surrogate key, which is stable and efficient.
• email and ssn become our Alternate Keys. We enforce their uniqueness using UNIQUE constraints.

CREATE TABLE Employees (
    employee_id INT AUTO_INCREMENT,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL,
    ssn VARCHAR(11) NOT NULL, -- Social Security Number
    department_id INT,

    -- Define the Primary Key
    PRIMARY KEY (employee_id),

    -- Define Alternate Keys
    UNIQUE (email),
    UNIQUE (ssn)
);

-- Now, let's create a departments table and link it
CREATE TABLE Departments (
    department_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE
);

-- Add the Foreign Key constraint to the Employees table
ALTER TABLE Employees
ADD CONSTRAINT fk_department
FOREIGN KEY (department_id)
REFERENCES Departments(department_id)
ON DELETE SET NULL; -- If a department is deleted, set employee's department to NULL

Verification

Let’s check the structure of our Employees table using DESCRIBE.

DESCRIBE Employees;

The output shows our key structure clearly.

Field	Type	Null	Key	Default	Extra
employee_id	int	NO	PRI	NULL	auto_increment
first_name	varchar(100)	NO		NULL
last_name	varchar(100)	NO		NULL
email	varchar(255)	NO	UNI	NULL
ssn	varchar(11)	NO	UNI	NULL
department_id	int	YES	MUL	NULL

Key column explanation: PRI for Primary Key, UNI for Unique (our Alternate Keys), and MUL for Multiple (indicating it’s an indexed column, in this case, a Foreign Key).

Best Practices for Key Selection

• Prefer Surrogate Keys: Use an AUTO_INCREMENT integer (employee_id) as the Primary Key. This is called a surrogate key. It’s stable (never changes), simple, and efficient for joins.
• Avoid Natural Keys as Primary Keys: A “natural key” is a key that has business meaning (like an email address or SSN). These can change (a person changes their email), which creates major problems if it’s a Primary Key used in Foreign Key relationships. Use them as Alternate Keys instead.
• Enforce All Uniqueness: If a column must be unique (like email), always add a UNIQUE constraint. Don’t rely on application logic to enforce this. This guarantees data integrity at the database level.
• Be Deliberate with Foreign Key Actions: Carefully choose your ON DELETE and ON UPDATE actions. RESTRICT (the default) is safest. CASCADE is powerful but can lead to unintentional mass deletions. SET NULL is a good compromise if the relationship is optional.