PRIMARY KEY
In this page:
column_name datatype PRIMARY KEY
PRIMARY KEY (column1, column2)Understanding PRIMARY KEY
A PRIMARY KEY uniquely identifies every row in a table and, unlike a plain UNIQUE column, can never contain NULL — it's the anchor MySQL and your application both rely on to reference a specific record unambiguously.
Example: Understanding PRIMARY KEY
CREATE TABLE users (id INT PRIMARY KEY);Composite Primary Keys
A composite primary key spans two or more columns together, used when no single column is unique on its own — a classic example is an order_items table keyed on (order_id, product_id) combined.
Example: Composite Primary Keys
CREATE TABLE order_items (
order_id INT,
product_id INT,
PRIMARY KEY (order_id, product_id)
);Adding PRIMARY KEY to Existing Tables
Adding a primary key to an existing table via ALTER TABLE requires that the target column already contains no NULLs and no duplicate values, or the operation fails outright.
Example: Adding PRIMARY KEY to Existing Tables
ALTER TABLE users ADD PRIMARY KEY (id);Dropping a PRIMARY KEY
Because a table can only ever have one primary key, dropping it doesn't require naming the column — MySQL already knows exactly which constraint you mean.
Example: Dropping a PRIMARY KEY
ALTER TABLE users DROP PRIMARY KEY;Best Practices for Primary Keys
Nearly every well-designed table should have a primary key: it speeds up lookups and joins dramatically and gives every row a stable, unambiguous identity to reference from other tables.
Example: Best Practices for Primary Keys
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
email VARCHAR(100)
);- Trying to add a second primary key to a table, when there can be only one (use a composite key instead).
- Using a value that changes, like an email, as the primary key, which then breaks references from other tables.
- Inserting
NULLor a duplicate into a primary key column, which is rejected.
Chapter Quiz — Complete all 8 topics to unlock
0/8 topics done
Complete these topics first: