Alternate Key

Why interviewers love this: it checks whether you know the real relational-model term and the SQL feature that actually enforces it.

Question: What is an alternate key in SQL?

Answer: An alternate key is a column, or group of columns, that can uniquely identify a row, but was not chosen as the primary key. In plain SQL, you usually enforce it with a UNIQUE constraint, and if you want it to behave like a true key, you also make it NOT NULL. A table can have more than one alternate key.

Interview-Ready Answer: I’d say an alternate key is a candidate key that was not selected as the primary key. It still uniquely identifies each row, so in SQL I normally enforce it with a UNIQUE constraint, often together with NOT NULL. A common example is a user table where user_id is the primary key, but email and username are alternate keys.

What an alternate key really means

Detailed Explanation: In relational design, a candidate key is any set of columns that can uniquely identify a row. A primary key is the one candidate key we pick as the main identifier. Every other candidate key becomes an alternate key. The big gotcha is that SQL does not have a separate ALTERNATE KEY keyword; it is a design concept, not a special command.

How it works under the hood

1. First, you identify all the columns that are unique enough for the business, such as email, username, or a composite like (country_code, tax_id).
2. Next, you choose one of them as the primary key, usually the most stable and compact choice. Many teams use a surrogate key, which is an artificial ID like user_id.
3. The remaining candidate keys become alternate keys. They still matter because the database must prevent duplicates in those columns.
4. In SQL, the database enforces this with a UNIQUE constraint. Most engines back that with a unique index, which is a data structure that keeps values sorted and makes duplicate checks fast.
5. On every INSERT or UPDATE, the engine looks up the new value in that index. If it finds an existing match, it rejects the change.

When and why to use it

Use an alternate key when the business has another real-world identifier that must never repeat. Good examples are email addresses, employee numbers, product codes, VINs, or passport numbers. This helps in two ways: it protects data quality, and it gives you a safe lookup path for queries and joins.

Performance and practical notes

Uniqueness checks are fast because the database usually uses a B-tree index, a tree-shaped structure that keeps lookups close to O(log n). In real terms, even millions of rows are manageable because the engine only follows a few index levels, often just a handful of page reads when the index is cached in memory. The trade-off is write cost: every extra unique constraint adds some overhead on insert and update, because the database must check the new value before it can commit it.

Important edge cases

• An alternate key can be composite, meaning it uses more than one column.
• If the column can be NULL, some databases treat that differently under UNIQUE; to make it a true business key, add NOT NULL.
• A table can have zero alternate keys if it has only one candidate key and that one is the primary key.
• Updating an alternate key value can fail if the new value already exists elsewhere.

Memory trick: think “one passport, many valid IDs” — the primary key is the passport you pick for the table, and alternate keys are the other unique IDs that still prove identity.

Real-World Example: In a checkout or account-service database, a users table often has an internal user_id primary key plus alternate keys like email and username. The app uses user_id for joins because it is short and stable, but customers log in with email, so that email must also stay unique.

What goes wrong when this is misunderstood? Imagine a team only adds a primary key and forgets the alternate key constraint on email. Two accounts can accidentally be created with the same email address. Then password reset emails may point to the wrong person, login lookups can return the wrong row, and support sees complaints like “I can see another user’s profile” or “my reset link stopped working.” In logs, you might notice duplicate email rows, mismatched account IDs, or application errors during account recovery. The bug is subtle because the database looks healthy until a business rule is violated.

Follow-up & Tricky Questions:

• How is an alternate key different from a candidate key? A candidate key is any unique identifier; an alternate key is a candidate key you did not choose as the primary key. So every alternate key is a candidate key, but not every candidate key is alternate.
• How do you enforce an alternate key in SQL? Use a UNIQUE constraint, and add NOT NULL if the column must never be missing. That makes the rule explicit and protects your data on every insert and update.
• Can a table have more than one alternate key? Yes. If a table has multiple candidate keys and you choose only one as primary, all the others are alternate keys.
• Can an alternate key be composite? Yes, it can be made of multiple columns, such as (country_code, tax_id). The pair together must be unique, even if neither column is unique by itself.
• Can a foreign key reference an alternate key? Yes, if the referenced columns are guaranteed unique, usually by a primary key or a unique constraint. This is useful when another table naturally points to a business identifier instead of an internal ID.
• Tricky: Does UNIQUE automatically make a column a true alternate key? Not always. In many databases, UNIQUE alone may still allow NULL values, so for a real key-like rule you usually want UNIQUE NOT NULL.
• Tricky: If the primary key is a surrogate ID, are natural unique columns alternate keys? Yes, if they uniquely identify the row and are not the chosen primary key. For example, email and employee_number are common alternate keys when id is the primary key.
• Tricky: If there is only one candidate key, do you still have an alternate key? No. If that one candidate key is selected as the primary key, there are no alternate keys left.

Common Mistakes:

• Thinking alternate key is a SQL keyword. Correction: it is a relational design term; in SQL you usually enforce it with UNIQUE.
• Forgetting NOT NULL. Correction: if the column is a true identity for a row, make it non-null as well as unique.
• Using a changing value as the main key. Correction: keep the primary key stable and use alternate keys for business identifiers like email or SKU.
• Assuming one unique column means one alternate key only. Correction: a table can have multiple alternate keys, including composite ones.

Memory Hook: Primary key = the ID card you choose to show first. Alternate key = another official ID that also proves who the row is.

Cheat Sheet:

• Alternate key = candidate key not chosen as primary.
• SQL usually uses UNIQUE to enforce it.
• Add NOT NULL for a strict business key.
• Can be single-column or composite.
• Multiple alternate keys are allowed.
• It helps prevent duplicate business records.

Practice Tasks:

• Create a products table with product_id as the primary key and sku as an alternate key.
• Add a composite alternate key to an order_items table using (order_id, line_no).
• Try inserting a duplicate business value and watch the database reject it, then explain why the error is useful.