by
SQL Tutorial › Database Design › PRIMARY KEY

SQL PRIMARY KEY

BeginnerLesson 53 of 699 min read7 queries run on MySQL 8.4

The primary key is the column (or set of columns) that identifies each row uniquely. It cannot be NULL, cannot repeat, and is what other tables point to with foreign keys. Every table should have one, and choosing it well is one of the first decisions in database design.

Advertisement
In this lesson
  • Define single-column and composite primary keys
  • Understand the errors a primary key prevents
  • Choose between natural and surrogate keys
  • Add, drop and inspect a primary key

Define a primary key

SQL
CREATE TABLE books (  book_id INT PRIMARY KEY,  isbn    CHAR(13) NOT NULL UNIQUE,  title   VARCHAR(100) NOT NULL);INSERT INTO books VALUES (1, '9780134685991', 'Effective Java'), (2, '9781492056355', 'Learning SQL');SELECT * FROM books;
ResultMySQL 8.4
mysql> CREATE TABLE books (
Query OK, 0 rows affected
mysql> INSERT INTO books VALUES (1, '9780134685991', 'Effective Java'), (2, '…
Query OK, 2 rows affected
mysql> SELECT * FROM books
book_idisbntitle
19780134685991Effective Java
29781492056355Learning SQL
2 rows in set
How this query works, step by step
  1. book_id INT PRIMARY KEY makes book_id unique and NOT NULL.
  2. isbn is also unique, but it is a business value; the short integer book_id is the key other tables will reference.

What a primary key prevents

MySQL
CREATE TABLE books (book_id INT PRIMARY KEY, title VARCHAR(100) NOT NULL);INSERT INTO books VALUES (1, 'Effective Java');INSERT INTO books VALUES (1, 'Learning SQL');
ResultMySQL 8.4
mysql> CREATE TABLE books (book_id INT PRIMARY KEY, title VARCHAR(100) NOT NU…
Query OK, 0 rows affected
mysql> INSERT INTO books VALUES (1, 'Effective Java')
Query OK, 1 row affected
mysql> INSERT INTO books VALUES (1, 'Learning SQL')
ERROR 1062 (23000): Duplicate entry '1' for key 'books.PRIMARY'
MySQL
CREATE TABLE books (book_id INT PRIMARY KEY, title VARCHAR(100) NOT NULL);INSERT INTO books VALUES (NULL, 'Learning SQL');
ResultMySQL 8.4
mysql> CREATE TABLE books (book_id INT PRIMARY KEY, title VARCHAR(100) NOT NU…
Query OK, 0 rows affected
mysql> INSERT INTO books VALUES (NULL, 'Learning SQL')
ERROR 1048 (23000): Column 'book_id' cannot be null
How this query works, step by step
  1. Error 1062: a second row with book_id = 1 is a duplicate entry for key PRIMARY.
  2. Error 1048: the key cannot be NULL.

A table can have only one primary key:

MySQL
CREATE TABLE t (a INT PRIMARY KEY, b INT PRIMARY KEY);
ResultMySQL 8.4
ERROR 1068 (42000): Multiple primary key defined

Composite primary key

When no single column is unique, the key can be a combination. In order_items, an order has many products and a product appears in many orders, but each (order_id, product_id) pair appears once:

MySQL
SHOW KEYS FROM order_items WHERE Key_name = 'PRIMARY';
ResultMySQL 8.4
TableNon_uniqueKey_nameSeq_in_indexColumn_nameCollationCardinalitySub_partPackedNullIndex_typeCommentIndex_commentVisibleExpression
order_items0PRIMARY1order_idA1NULLNULLBTREEYESNULL
order_items0PRIMARY2product_idA1NULLNULLBTREEYESNULL
2 rows in set
MySQL
INSERT INTO order_items (order_id, product_id, quantity) VALUES (1001, 2, 3);
ResultMySQL 8.4
ERROR 1062 (23000): Duplicate entry '1001-2' for key 'order_items.PRIMARY'
How this query works, step by step
  1. SHOW KEYS lists the two columns of the primary key in order (Seq_in_index 1 and 2).
  2. Order 1001 already contains product 2, so the same pair is rejected; to change the quantity, UPDATE the existing line.

Natural vs surrogate keys

Natural keySurrogate key
Exampleemail, ISBN, PAN numbercustomer_id INT AUTO_INCREMENT
Meaningreal-world valuenone, just a number
Can change?yes (emails change)never
Sizeoften long text4 or 8 bytes

Most designs use a surrogate primary key (often AUTO_INCREMENT) and protect the natural identifier with a UNIQUE constraint, as the books table above does with isbn.

Tip

In InnoDB, rows are physically stored in primary-key order and every secondary index stores the primary key. A short, ever-increasing key (such as an auto-increment integer) keeps tables and indexes compact and inserts fast.

Add or drop a primary key

MySQL
CREATE TABLE visits (visitor VARCHAR(30), visit_date DATE, pages INT);INSERT INTO visits VALUES ('asha', '2026-01-01', 3), ('asha', '2026-01-02', 5);ALTER TABLE visits ADD PRIMARY KEY (visitor, visit_date);SHOW KEYS FROM visits WHERE Key_name = 'PRIMARY';
ResultMySQL 8.4
mysql> CREATE TABLE visits (visitor VARCHAR(30), visit_date DATE, pages INT)
Query OK, 0 rows affected
mysql> INSERT INTO visits VALUES ('asha', '2026-01-01', 3), ('asha', '2026-01…
Query OK, 2 rows affected
mysql> ALTER TABLE visits ADD PRIMARY KEY (visitor, visit_date)
Query OK, 0 rows affected
mysql> SHOW KEYS FROM visits WHERE Key_name = 'PRIMARY'
TableNon_uniqueKey_nameSeq_in_indexColumn_nameCollationCardinalitySub_partPackedNullIndex_typeCommentIndex_commentVisibleExpression
visits0PRIMARY1visitorA1NULLNULLBTREEYESNULL
visits0PRIMARY2visit_dateA2NULLNULLBTREEYESNULL
2 rows in set
How this query works, step by step
  1. The table is created without a key.
  2. ALTER TABLE ... ADD PRIMARY KEY adds a composite key; it works because the existing rows are unique and not NULL.
  3. ALTER TABLE visits DROP PRIMARY KEY; would remove it again.

Quick quiz

1. How many primary keys can a table have?

2. Can a primary key column contain NULL?

3. What is a surrogate key?

Primary key FAQ

What is a primary key in SQL?

A column or group of columns whose values uniquely identify each row. It must be unique and cannot be NULL.

What is a composite primary key?

A primary key made of two or more columns, where only the combination must be unique, e.g. (order_id, product_id).

What is the difference between primary key and unique key?

A table has one primary key and it cannot contain NULL; it can have many unique keys, which may contain NULLs.

Should every table have a primary key?

Yes. Without one, rows cannot be identified reliably, and InnoDB creates a hidden key that you cannot use.