Alternate Key

Definition

An alternate key is any candidate key that was NOT chosen to be the primary key. If a table has candidate keys {id} and {email}, and {id} is chosen as primary key, then {email} becomes an alternate key.

How It Works

sql
CREATE TABLE students (
  id INT PRIMARY KEY,
  email VARCHAR(150) UNIQUE NOT NULL,  -- alternate key: enforced with UNIQUE, not PRIMARY KEY
  name VARCHAR(100) NOT NULL
);

In SQL, an alternate key is implemented with a UNIQUE constraint (optionally combined with NOT NULL if the business rule requires a value to always be present) rather than PRIMARY KEY — SQL reserves the PRIMARY KEY keyword specifically for the one chosen key. The alternate key still fully enforces uniqueness; it's a real, enforced key, just not the table's "headline" identifier used for foreign-key relationships elsewhere.

Edge Cases and Pitfalls

• Unlike PRIMARY KEY, a plain UNIQUE constraint (used for alternate keys) does NOT automatically forbid NULL — most engines allow multiple NULLs in a UNIQUE column (NULL is not considered "equal" to another NULL), which is a common source of confusion if you expected UNIQUE to behave exactly like PRIMARY KEY.
• A table can have several alternate keys at once (if it has several unchosen candidate keys) — there's no limit of one, unlike the primary key.
• Foreign keys in OTHER tables typically reference the primary key, not an alternate key — though most SQL dialects technically allow a foreign key to reference any UNIQUE column, doing so is less common and can be more error-prone to maintain.

Key Takeaways

• Alternate key = a candidate key that lost out to the primary key, implemented via UNIQUE.
• A table can have zero, one, or many alternate keys.
• UNIQUE allows NULLs (usually multiple); PRIMARY KEY never does — this is the practical SQL-level difference.