MySQL Primary Key

MySQL Primary Key Constraint

A Primary Key in MySQL is a column (or a set of columns) that uniquely identifies each row in a table. It ensures that no duplicate or NULL values exist in the specified column.

1. Why Use a Primary Key?

✔ Uniquely Identifies Each Record – Ensures every row is unique.
✔ Ensures Data Integrity – Prevents duplicate and NULL values.
✔ Improves Indexing & Performance – Automatically creates an index for fast searching.
✔ Establishes Relationships – Used in foreign key constraints.

2. How to Create a Primary Key in MySQL?

A primary key is created using the PRIMARY KEY constraint while defining a table.

Syntax

PRIMARY KEY (column_name)

Example: Creating a Primary Key on a Single Column

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    department VARCHAR(50)
);

✔ Here, id is the primary key, meaning:

Each id value must be unique.
id cannot be NULL.

3. Defining a Primary Key on Multiple Columns (Composite Key)

A composite primary key consists of two or more columns.

Example:

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

✔ The combination of order_id and product_id must be unique.
✔ Neither order_id nor product_id can be NULL.

4. How to Add a Primary Key to an Existing Table?

If a table is already created, use ALTER TABLE to add a primary key.

Adding a Primary Key

ALTER TABLE employees ADD PRIMARY KEY (id);

Adding a Composite Primary Key

ALTER TABLE orders ADD PRIMARY KEY (order_id, product_id);

5. How to Remove a Primary Key?

To remove a primary key, use ALTER TABLE DROP PRIMARY KEY.

Removing a Primary Key

ALTER TABLE employees DROP PRIMARY KEY;

⚠ Important: If the primary key is auto-incremented, you may need to disable it before dropping the key:

ALTER TABLE employees MODIFY id INT;
ALTER TABLE employees DROP PRIMARY KEY;

6. Primary Key vs. Unique Key

Feature	Primary Key	Unique Key
Uniqueness	Ensures unique values	Ensures unique values
NULL Values	Not allowed	Allowed
Number of Keys	Only one per table	Multiple allowed

7. How to Check Primary Keys in a Table?

To list the primary keys of a table:

SHOW INDEX FROM employees WHERE Key_name = 'PRIMARY';

8. Common Primary Key Errors & Solutions

Error Message	Cause	Solution
`Duplicate entry for key PRIMARY`	Trying to insert duplicate primary key values.	Ensure values are unique before inserting.
`ERROR 1068: Multiple primary key defined`	Trying to define multiple primary keys in one table.	A table can have only one primary key. Use `UNIQUE` instead.
`ERROR 1171: All parts of a PRIMARY KEY must be NOT NULL`	One or more columns in the composite key allow `NULL`.	Define all primary key columns as `NOT NULL`.

9. Summary

A primary key uniquely identifies each row and cannot be NULL.
A table can have only one primary key.
A composite primary key is created using multiple columns.
To modify or remove a primary key, use ALTER TABLE.
Use SHOW INDEX FROM table_name; to check primary keys.

Would you like a real-world example of using primary keys in an application? 🚀

Souy Soeng

MySQL Primary Key