Primary keys
40 min · Beginner
Need help? Ask a tutor on WhatsApp (opens in a new tab)A primary key is the column (or set of columns) that names each row of a table uniquely, like a roll number in a class register. MySQL refuses any row whose key is missing or already used, so every row can always be found, changed or linked to without confusion.
What a primary key guarantees
A primary key is the column (or set of columns) whose value is different in every row and is never NULL. MySQL checks both rules on every insert and update, so each row can always be found, changed or linked to.
Example 1 stores room 101 twice in a table with no key, and MySQL accepts it. Example 2 adds a primary key, and the repeat is refused.
Example 1 · No key: two rows nobody can tell apart
CREATE TABLE hostel_rooms_nokey ( room_no INT, block CHAR(1), beds TINYINT);
INSERT INTO hostel_rooms_nokey VALUES (101, 'A', 3);INSERT INTO hostel_rooms_nokey VALUES (102, 'A', 2);INSERT INTO hostel_rooms_nokey VALUES (101, 'A', 3); -- typed twice by mistake
SELECT * FROM hostel_rooms_nokey ORDER BY room_no;+---------+-------+------+ | room_no | block | beds | +---------+-------+------+ | 101 | A | 3 | | 101 | A | 3 | | 102 | A | 2 | +---------+-------+------+
What this code does
- MySQL accepts the same room twice because nothing tells it that
room_nomust be unique. - The two
101rows are identical, so a statement cannot point at only one of them to fix the mistake. ORDER BY room_noonly sorts the result so it prints the same way every time; the Querying module covers it.
Example 2 · With a primary key: MySQL refuses the repeat
CREATE TABLE hostel_rooms ( room_no INT PRIMARY KEY, block CHAR(1), beds TINYINT);
DESCRIBE hostel_rooms;
INSERT INTO hostel_rooms VALUES (101, 'A', 3);INSERT INTO hostel_rooms VALUES (102, 'A', 2);INSERT INTO hostel_rooms VALUES (101, 'A', 3); -- same mistake again+---------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+---------+------+-----+---------+-------+ | room_no | int | NO | PRI | NULL | | | block | char(1) | YES | | NULL | | | beds | tinyint | YES | | NULL | | +---------+---------+------+-----+---------+-------+ ERROR 1062 (23000): Duplicate entry '101' for key 'hostel_rooms.PRIMARY'
What this code does
DESCRIBEshowsPRIin theKeycolumn andNOunderNull: MySQL maderoom_noNOT NULL by itself.- The first two inserts work. The third repeats
101, so MySQL stops it with error 1062 and the table keeps only two rows. - The mysql client stops at the first error, so this block ends there. A lesson block that shows an error always ends with it.
Without a key, a table can hold two rows that look exactly the same. Then nobody can say "change that row" or "delete that row", because there is no way to point at just one of them. A table has at most one primary key. You write PRIMARY KEY after the column type, and MySQL adds NOT NULL to that column for you.
Predict the output
Will this INSERT work?
Think about the primary key of the customers table, then pick what MySQL does.
Round 1 of 2
Natural keys versus surrogate keys
A surrogate key is a number the database makes up only to name a row, usually an INT AUTO_INCREMENT id. A natural key is a real-world value, such as an email or a PAN, that could do the same job.
A name or an email can repeat or change, so Example 3 names each row with a number that MySQL generates instead.
Example 3 · A surrogate key for players who share a name
CREATE TABLE ipl_players ( player_id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(60) NOT NULL, team VARCHAR(30) NOT NULL);
-- Two different players with the same nameINSERT INTO ipl_players (name, team) VALUES ('Rahul Sharma', 'Mumbai Indians');INSERT INTO ipl_players (name, team) VALUES ('Rahul Sharma', 'Punjab Kings');INSERT INTO ipl_players (name, team) VALUES ('Shubman Gill', 'Gujarat Titans');
SELECT * FROM ipl_players ORDER BY player_id;+-----------+--------------+----------------+ | player_id | name | team | +-----------+--------------+----------------+ | 1 | Rahul Sharma | Mumbai Indians | | 2 | Rahul Sharma | Punjab Kings | | 3 | Shubman Gill | Gujarat Titans | +-----------+--------------+----------------+
What this code does
- If
namewere the key, the second Rahul Sharma could never be stored. A surrogateplayer_idkeeps the two players apart. - The inserts leave out
player_id;AUTO_INCREMENTfills in 1, 2, 3, so the key is never typed by hand. - The id means nothing outside the database, so it never needs to change, even if a player changes team or spelling.
A surrogate key column is usually called id or customer_id.
Natural keys look attractive but have three problems. Real values change (people change email and mobile numbers). Some are not truly unique (two IPL players can share a name). And some are private (an Aadhaar number should not be copied into many tables). A surrogate key never changes, never repeats and means nothing outside the database. That is why most application tables, including every ShopLite table, use one. Keep the real-world value as a normal column; the UNIQUE and CHECK lesson shows how to stop it repeating too.
Composite primary keys
A composite primary key is a primary key made of two or more columns together. Each column alone may repeat; only the combination must be new.
A student can have many subjects, and a subject many students, so neither column is unique alone. The snippet shows the line; Example 4 stores marks per subject.
-- Wrong: PRIMARY KEY written on two columns separatelyCREATE TABLE exam_marks_wrong ( roll_no INT PRIMARY KEY, subject_code CHAR(5) PRIMARY KEY, marks TINYINT);ERROR 1068 (42000): Multiple primary key defined
Example 4 · A composite key for marks per subject
CREATE TABLE exam_results ( roll_no INT, subject_code CHAR(5), marks TINYINT NOT NULL, PRIMARY KEY (roll_no, subject_code));
INSERT INTO exam_results VALUES (1, 'MA101', 78);INSERT INTO exam_results VALUES (1, 'CS201', 91); -- same student, new subjectINSERT INTO exam_results VALUES (2, 'MA101', 64); -- same subject, new student
SELECT * FROM exam_results ORDER BY roll_no, subject_code;
INSERT INTO exam_results VALUES (1, 'MA101', 80); -- same pair again+---------+--------------+-------+ | roll_no | subject_code | marks | +---------+--------------+-------+ | 1 | CS201 | 91 | | 1 | MA101 | 78 | | 2 | MA101 | 64 | +---------+--------------+-------+ ERROR 1062 (23000): Duplicate entry '1-MA101' for key 'exam_results.PRIMARY'
What this code does
- Roll 1 appears twice and MA101 appears twice, and both are fine: only the pair (roll_no, subject_code) must be new.
- The last insert repeats the pair (1, MA101), so MySQL refuses it. The message shows the clashing pair as
'1-MA101'. - The key line
PRIMARY KEY (roll_no, subject_code)sits after the columns because it covers more than one column.
You write a composite key as a separate line at the end of the column list: PRIMARY KEY (roll_no, subject_code).
Use it when a row is naturally named by a pair: one student in one subject, one product in one order, one seat in one show. The practice tables enrollments (student_id, course_id) and order_items (order_id, product_id) are built this way. The order of the columns matters for speed, not for the rule. Put the column you search by most often first; the Indexes module explains why.
PRIMARY KEY after two columns does not make a composite key: it asks for two primary keys, and MySQL stops with Multiple primary key defined.Reading the Duplicate entry error
ERROR 1062 Duplicate entry is the error MySQL raises when a new or changed row would repeat a PRIMARY KEY or UNIQUE value. The message names the clashing value and the key that refused it.
Signing up twice with the same value fails with ERROR 1062. Example 5 adds a primary key to a table that already has repeats and shows what the message names.
Example 5 · Adding a primary key to a table that has repeats
CREATE TABLE fest_passes ( pass_no INT, student VARCHAR(40));
INSERT INTO fest_passes VALUES (5001, 'Anu');INSERT INTO fest_passes VALUES (5002, 'Bilal');INSERT INTO fest_passes VALUES (5001, 'Charan'); -- a pass printed twice
ALTER TABLE fest_passes ADD PRIMARY KEY (pass_no);ERROR 1062 (23000): Duplicate entry '5001' for key 'fest_passes.PRIMARY'
What this code does
ALTER TABLE ... ADD PRIMARY KEYchecks the rows already in the table, and pass 5001 is there twice.- MySQL gives the same error 1062 as for an insert and leaves the table unchanged.
- The fix is to correct the data first (give Charan a new pass number), or to add a new surrogate key column, as practice 6 does.
Error 1062 is the most common error an application sees, for example when a user signs up twice with the same email.
Read the message from left to right:
1062is MySQL's own error number; search it in the manual.23000is the standard SQLSTATE class for "a data rule was broken"; every database uses it.'1-2'is the value that clashed; for a composite key the parts are joined with-.'exam_results.PRIMARY'names the table and the key that refused it.
Next step
Check your understanding
11 quick questions