Primary Key

Definition

A primary key is the ONE candidate key that a database designer formally chooses to be a table's main, authoritative identifier. Once chosen, the DBMS enforces two rules on it automatically: values must be unique, and values must be `NOT NULL`.

Running example used throughout this chapter — a students(id, name, email, department_id) table:

How It Works

sql
CREATE TABLE students (
  id INT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(150) UNIQUE,
  department_id INT
);

Declaring id INT PRIMARY KEY does two things automatically, with no extra syntax needed: it forbids duplicate id values (uniqueness) AND forbids id from ever being NULL (this second rule is called entity integrity, covered in 5.10). This is the one meaningful difference between PRIMARY KEY and a plain UNIQUE constraint (5.9) — UNIQUE alone still permits NULL.

Most engines also automatically create an index on the primary key column(s), since the primary key is the column most frequently used to look up individual rows — this makes primary-key lookups fast by default, as a side effect rather than something you have to configure separately.

Edge Cases and Pitfalls

• A table can have only ONE primary key (though that one primary key can span multiple columns — a composite primary key, 5.6).
• Declaring PRIMARY KEY twice on the same table is a schema error, not a way to have "two primary keys."
• Once other tables reference this table via foreign keys (5.5), changing the primary key's values (or dropping the column) becomes a much bigger operation, since every referencing row would become invalid — this is a real practical reason to prefer a stable surrogate key (5.8) as primary key over a natural value that might need to change.

Key Takeaways

• Primary key = the one chosen candidate key; automatically UNIQUE + NOT NULL.
• A table has exactly one primary key, which may be composite (multiple columns).
• Changing primary key values later is risky once foreign keys reference it — favor stable values.