MySQL PRIMARY KEY Constraint
MySQL PRIMARY KEY Constraint
The PRIMARY KEY constraint in MySQL is used to uniquely identify each row in a table.
It ensures uniqueness and non-NULL values for the key column(s).
What is a PRIMARY KEY?
A column (or set of columns) that uniquely identifies a row
No duplicate values
NULL values are not allowed
Only one PRIMARY KEY per table
Commonly used with AUTO_INCREMENT for ID columns.
Why Use PRIMARY KEY?
- Ensures data uniqueness
- Improves query performance (indexed automatically)
- Required for FOREIGN KEY references
- Essential for relational design
Basic Syntax
Simple Example
Insert Data
Duplicate not allowed:
- Error: Duplicate entry for PRIMARY KEY
PRIMARY KEY with AUTO_INCREMENT
- MySQL automatically generates unique
emp_id.
Composite PRIMARY KEY (Multiple Columns)
Used when combination of columns must be unique.
- Same student can enroll in different courses
- Same course can have different students
- Same student–course pair cannot repeat
Adding PRIMARY KEY Using ALTER TABLE
- Column must be NOT NULL and unique.
Dropping PRIMARY KEY
- Use carefully (may break foreign keys).
PRIMARY KEY vs UNIQUE (Interview Favorite)
| Feature | PRIMARY KEY | UNIQUE |
|---|---|---|
| Duplicate values | No | No |
| NULL allowed | No | Yes |
| Keys per table | 1 only | Multiple |
| Auto index | Yes | Yes |
| Foreign key reference | Yes | Yes |
Important Rules (Exam)
Only one PRIMARY KEY per table
Can be single-column or composite
Automatically creates an index
Frequently used as FOREIGN KEY target
Common Mistakes
- Trying to add two PRIMARY KEYs
- Allowing NULL in primary column
- Confusing UNIQUE with PRIMARY KEY
- Dropping PK without checking dependencies
Interview Questions & MCQs (Very Important)
Q1. What is the main purpose of PRIMARY KEY?
A) Sorting
B) Uniqueness
C) Indexing
D) Validation
Answer: B
Q2. How many PRIMARY KEYs can a table have?
A) One
B) Two
C) Many
D) None
Answer: A
Q3. Can PRIMARY KEY contain NULL?
A) Yes
B) No
Answer: B
Q4. Which statement creates a composite PRIMARY KEY?
A) Correct
B) Incorrect
Answer: A
Q5. PRIMARY KEY automatically creates:
A) FOREIGN KEY
B) UNIQUE index
C) VIEW
D) TRIGGER
Answer: B
Q6. Which constraint is required for FOREIGN KEY reference?
A) CHECK
B) NOT NULL
C) PRIMARY KEY / UNIQUE
D) DEFAULT
Answer: C
Real-Life Use Cases
- User ID
- Order ID
- Invoice number
- Employee ID
- Student registration number
Summary
PRIMARY KEY uniquely identifies rows
No NULLs, no duplicates
Only one per table (can be composite)
Automatically indexed
Foundation of relational databases
Very important for SQL exams & interviews
