Candidate, Primary, Alternate and Composite Keys

Concept

A key is a column, or a set of columns, whose value is unique across the table. Most tables have more than one. The terminology simply describes which role each key plays.

Syntax

CREATE TABLE employees (
    id          INT           NOT NULL AUTO_INCREMENT,
    email       VARCHAR(120)  NOT NULL,
    national_id VARCHAR(20)   NOT NULL,
    first_name  VARCHAR(50)   NOT NULL,
    CONSTRAINT pk_employees          PRIMARY KEY (id),           -- primary key
    CONSTRAINT uq_employees_email    UNIQUE (email),             -- alternate key
    CONSTRAINT uq_employees_national UNIQUE (national_id)        -- alternate key
);

Explanation

Choosing id as the primary key does not mean abandoning the other two. They are still genuine business rules - two employees must not share an email - and the only way to enforce them is a UNIQUE constraint. Dropping them because "the id is the key" is how duplicate people end up in a system.

Composite keys

CREATE TABLE assignments (
    employee_id INT,
    project_id  INT,
    hours       DECIMAL(6,1) NOT NULL DEFAULT 0,
    PRIMARY KEY (employee_id, project_id)
);

-- Enforces: an employee appears at most once per project
INSERT INTO assignments VALUES (2, 100, 340.5);
INSERT INTO assignments VALUES (2, 100, 12.0);   -- rejected, duplicate key
INSERT INTO assignments VALUES (2, 101, 80.0);   -- fine, different project

Column order in a composite key matters for indexing, not for uniqueness. PRIMARY KEY (employee_id, project_id) can be used to look up by employee_id alone, or by both - but not by project_id alone. Put the column you filter by most often first, and add a separate index on the other if you need it.

Important rules

• A candidate key is minimal. (id, name) is unique but not a candidate key, because id alone already is.
• A primary key is implicitly NOT NULL; alternate keys declared UNIQUE are not, so add NOT NULL where required.
• One primary key per table; any number of alternate keys.
• A composite key's uniqueness applies to the combination, never to the individual columns.
• A foreign key may reference a primary key or any unique key.

Common mistakes

• Adding a surrogate id and then dropping the UNIQUE constraints, allowing duplicate business records.
• Believing a composite UNIQUE (a, b) also makes a unique.
• Putting the columns of a composite key in an order that no query can use.
• Choosing a candidate key that is unique in today's data but not guaranteed unique by the business - names, phone numbers, addresses.

Best practices

• Identify every candidate key during design, then choose one as primary and enforce the rest with UNIQUE.
• Keep the primary key narrow - especially in MySQL/InnoDB, where it is copied into every secondary index.
• Order composite key columns by how queries filter, most selective and most frequently used first.
• Document why a particular candidate key was chosen; the next person will ask.

Practice

1. List the candidate keys of the sample orders table, and of order_items.
2. Explain why (order_id, product_id) is a better primary key for order_items than a new id column - and one reason you might add the id anyway.
3. Which alternate keys should customers have, and what breaks without them?