? MySQL PRIMARY KEY

MySQL Constraint

? Quick Overview

A PRIMARY KEY in MySQL is a constraint that uniquely identifies each row in a table. It guarantees:

Uniqueness – no two rows can have the same primary key value.
Non-nullability – primary key columns cannot contain NULL.
Fast lookups – MySQL automatically indexes the primary key for faster searches.

? Key Concepts

Every table should have a primary key to maintain data integrity.
A primary key can be a single column (simple key) or multiple columns (composite key).
Primary key values should be stable (not frequently changed).
Usually used on columns like ID, RollNo, AccountNo, etc.

? Syntax

Defining a primary key directly on a column inside CREATE TABLE:

? View Basic PRIMARY KEY Syntax

-- Define a table with a single-column PRIMARY KEY
CREATE TABLE table_name (
ID INT PRIMARY KEY,
Name VARCHAR(100)
);

Defining a primary key separately (useful for composite keys):

? View Composite PRIMARY KEY Syntax

-- Define a table with a composite PRIMARY KEY
CREATE TABLE table_name (
OrderID INT,
ProductID INT,
Quantity INT,
PRIMARY KEY (OrderID, ProductID)
);

? Example: Employees Table

Here is a simple table where ID uniquely identifies each employee.

? View Employees Table Example

-- Create Employees table with PRIMARY KEY on ID
CREATE TABLE Employees (
ID INT PRIMARY KEY,
Name VARCHAR(100),
Age INT,
Salary DECIMAL(10,2)
);

? Explanation & Behavior

ID as PRIMARY KEY – each employee gets a unique ID.
No duplicates – inserting the same ID again will cause an error.
No NULL – you must always provide a value for ID.

? What happens when you insert data?

Valid inserts:

INSERT INTO Employees VALUES (1, 'Alice', 28, 45000.00);
INSERT INTO Employees VALUES (2, 'Bob', 32, 52000.00);

If you try:

INSERT INTO Employees VALUES (1, 'Charlie', 30, 48000.00);

MySQL will reject it because ID = 1 already exists. This is how the PRIMARY KEY protects your table from duplicate records.

? Use Case: Composite Primary Key

Composite primary keys are handy in junction tables, like when you store which products belong to which order:

? View OrderItems Composite Key Example

-- Each (OrderID, ProductID) pair must be unique
CREATE TABLE OrderItems (
OrderID INT,
ProductID INT,
Quantity INT,
PRIMARY KEY (OrderID, ProductID)
);

✅ Tips & Best Practices

Choose a primary key that never changes (e.g., numeric ID, not a name).
Prefer INT or BIGINT types for primary keys—they are efficient and fast.
Use AUTO_INCREMENT with integer primary keys to generate IDs automatically.
Use composite primary keys when a single column is not enough to uniquely identify a row.
Always define a primary key to avoid duplicate or ambiguous records.

? Try It Yourself

Create a table Students with StudentID as the primary key and insert some rows.
Try inserting two rows with the same StudentID and observe the error MySQL throws.
Create a table CourseEnrollment with a composite key on (StudentID, CourseID).
Experiment by changing a primary key value and notice how it affects related data (if any foreign keys exist).