PRIMARY KEY

In this page:

Understanding PRIMARY KEY
Composite Primary Keys
Adding PRIMARY KEY to Existing Tables
Dropping a PRIMARY KEY
Best Practices for Primary Keys

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

sql

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

sql

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

sql

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

sql

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

sql

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(100)
);

🔒

Chapter Quiz — Complete all 8 topics to unlock

0/8 topics done

Complete these topics first:

← Previous Lesson UNIQUE Constraint Next Lesson → FOREIGN KEY