MySQL PRIMARY KEY Constraint

Every table in a well-designed database should have a primary key — a column (or a combination of columns) that uniquely identifies each row. No two rows can share the same primary key value, and a primary key column can never be NULL.

Think of a primary key like a national ID number — it is unique to one person and cannot be left blank. In a database table, it plays the same role: one value, one row, no exceptions.

---

What Makes PRIMARY KEY Special?

A PRIMARY KEY automatically enforces two rules at once:

• NOT NULL — the column cannot be empty.
• UNIQUE — no two rows can have the same value.

Additionally, MySQL automatically creates an index on the primary key column, which makes searching and joining tables by that column extremely fast.

---

Syntax

There are two ways to define a primary key — inline on the column, or as a separate table-level declaration:

-- Inline
column_name data_type NOT NULL PRIMARY KEY

-- Table-level (recommended for clarity)
PRIMARY KEY (column_name)

---

Single-Column PRIMARY KEY

This is the most common pattern — an integer column that automatically increments with each new row:

CREATE TABLE products (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(150) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    PRIMARY KEY (id)
);

Every time a new product is inserted, MySQL assigns the next available integer to id automatically. You never need to provide this value manually.

---

Composite PRIMARY KEY

A composite primary key uses two or more columns together to form a unique identifier. Neither column alone needs to be unique — the combination must be.

A classic example is an order items table, where one order can have many items, and one product can appear in many orders — but the same product should not appear twice in the same order:

CREATE TABLE order_items (
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    PRIMARY KEY (order_id, product_id)
);

---

Inserting Data — Primary Key in Action

When a table uses AUTO_INCREMENT on the primary key, you can omit that column from your INSERT statement and MySQL fills it in:

INSERT INTO products (name, price) VALUES ('Wireless Mouse', 850.00);
INSERT INTO products (name, price) VALUES ('USB Keyboard', 1200.00);
INSERT INTO products (name, price) VALUES ('HDMI Cable', 350.00);

MySQL assigns id values 1, 2, and 3 automatically.

If you try to insert a duplicate primary key value manually, MySQL rejects it:

INSERT INTO products (id, name, price) VALUES (1, 'Duplicate', 100.00);
-- ERROR 1062 (23000): Duplicate entry '1' for key 'products.PRIMARY'

---

Adding a PRIMARY KEY to an Existing Table

If a table was created without a primary key, you can add one later using ALTER TABLE:

ALTER TABLE products
ADD PRIMARY KEY (id);

---

Dropping a PRIMARY KEY

To remove a primary key from a table:

ALTER TABLE products
DROP PRIMARY KEY;

-- Step 1: Remove AUTO_INCREMENT first
ALTER TABLE products
MODIFY COLUMN id INT NOT NULL;

-- Step 2: Then drop the PRIMARY KEY
ALTER TABLE products
DROP PRIMARY KEY;

---

Key Points to Remember

• A PRIMARY KEY uniquely identifies every row in a table — no duplicates, no NULLs.
• Each table can have only one primary key.
• PRIMARY KEY automatically enforces both NOT NULL and UNIQUE on the column.
• MySQL creates an index on the primary key automatically, making lookups very fast.
• A composite primary key uses multiple columns — the combination must be unique.
• Most tables use an integer column with AUTO_INCREMENT as the primary key — this is the most common and recommended pattern.