MySQL Constraints: PRIMARY KEY, UNIQUE, NOT NULL, CHECK

A constraint is a rule that the database enforces for you, on every INSERT, UPDATE and DELETE, no matter which application or person sends the statement.

Bad data is cheap to prevent and expensive to clean up. A constraint is the cheapest prevention there is.

The constraints in MySQL

Constraint	Rule	Example
`NOT NULL`	The column must have a value	`email VARCHAR(100) NOT NULL`
`DEFAULT`	A value to use when none is given	`status VARCHAR(10) DEFAULT 'new'`
`PRIMARY KEY`	Unique, not null: identifies the row	`id INT PRIMARY KEY`
`UNIQUE`	No two rows may have the same value	`UNIQUE (email)`
`FOREIGN KEY`	The value must exist in another table	`FOREIGN KEY (customer_id) REFERENCES customers (id)`
`CHECK`	The value must satisfy a condition (MySQL 8.0.16+)	`CHECK (price >= 0)`

A table that uses all of them

query.sql

SQL

CREATE TABLE customers (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL
);

CREATE TABLE orders (
    id INT UNSIGNED AUTO_INCREMENT,
    order_number VARCHAR(20) NOT NULL,
    customer_id INT UNSIGNED NOT NULL,
    status VARCHAR(10) NOT NULL DEFAULT 'new',
    quantity INT NOT NULL,
    unit_price DECIMAL(8,2) NOT NULL,
    discount_percent DECIMAL(5,2) NOT NULL DEFAULT 0,
    PRIMARY KEY (id),
    CONSTRAINT uq_orders_number UNIQUE (order_number),
    CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers (id),
    CONSTRAINT chk_orders_quantity CHECK (quantity > 0),
    CONSTRAINT chk_orders_price CHECK (unit_price >= 0),
    CONSTRAINT chk_orders_discount CHECK (discount_percent BETWEEN 0 AND 100),
    CONSTRAINT chk_orders_status CHECK (status IN ('new', 'paid', 'shipped', 'cancelled'))
);

INSERT INTO customers (name) VALUES ('Maya');
INSERT INTO orders (order_number, customer_id, quantity, unit_price) VALUES ('ORD-001', 1, 2, 80.00);
SELECT id, order_number, status, quantity, discount_percent FROM orders;

Output

OUTPUT

+----+--------------+--------+----------+------------------+
| id | order_number | status | quantity | discount_percent |
+----+--------------+--------+----------+------------------+
|  1 | ORD-001      | new    |        2 |             0.00 |
+----+--------------+--------+----------+------------------+
1 row in set

The row is valid. status and discount_percent took their defaults.

Each rule in action

query.sql

SQL

INSERT INTO orders (order_number, customer_id, quantity, unit_price) VALUES ('ORD-001', 1, 1, 10.00);

Output

OUTPUT

ERROR 1062 (23000): Duplicate entry 'ORD-001' for key 'orders.uq_orders_number'

query.sql

SQL

INSERT INTO orders (order_number, customer_id, quantity, unit_price) VALUES ('ORD-002', 9, 1, 10.00);

Output

OUTPUT

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`learn`.`orders`, CONSTRAINT `fk_orders_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`))

query.sql

SQL

INSERT INTO orders (order_number, customer_id, quantity, unit_price) VALUES ('ORD-003', 1, 0, 10.00);

Output

OUTPUT

ERROR 3819 (HY000): Check constraint 'chk_orders_quantity' is violated.

query.sql

SQL

INSERT INTO orders (order_number, customer_id, quantity, unit_price, status) VALUES ('ORD-004', 1, 1, 10.00, 'lost');

Output

OUTPUT

ERROR 3819 (HY000): Check constraint 'chk_orders_status' is violated.

query.sql

SQL

INSERT INTO orders (order_number, customer_id, quantity) VALUES ('ORD-005', 1, 1);

Output

OUTPUT

ERROR 1364 (HY000): Field 'unit_price' doesn't have a default value

In order: a duplicate order number (UNIQUE), a customer that does not exist (FOREIGN KEY), a quantity of zero (CHECK), an unknown status (CHECK) and a missing price (NOT NULL without default).

See the constraints of a table

query.sql

SQL

SELECT constraint_name, constraint_type
FROM information_schema.table_constraints
WHERE table_schema = 'learn' AND table_name = 'orders'
ORDER BY constraint_type, constraint_name;

Output

OUTPUT

+---------------------+-----------------+
| CONSTRAINT_NAME     | CONSTRAINT_TYPE |
+---------------------+-----------------+
| chk_orders_discount | CHECK           |
| chk_orders_price    | CHECK           |
| chk_orders_quantity | CHECK           |
| chk_orders_status   | CHECK           |
| fk_orders_customer  | FOREIGN KEY     |
| PRIMARY             | PRIMARY KEY     |
| uq_orders_number    | UNIQUE          |
+---------------------+-----------------+
7 rows in set

Why not only validate in the application?

The application should validate too, to give friendly messages. But the database is the last line of defence:

Several applications, scripts and people write to the same database. Each would need the same rules.
Applications have bugs and race conditions. A uniqueness check done in code (“does this email exist?” then “insert”) can fail when two requests run at once. A UNIQUE constraint cannot.
Someone will run a manual UPDATE in production one day.
The constraints document the rules, and the optimiser can use some of them.

See Database vs Application Validation.

Name your constraints

Give constraints names (CONSTRAINT chk_orders_quantity CHECK ...). Error messages then say which rule failed, and you can drop or change a named constraint later. Unnamed ones get generated names such as orders_chk_1.

A naming convention that works well:

Type	Pattern	Example
Primary key	`pk_table`	`pk_orders` (MySQL always calls it `PRIMARY`)
Unique	`uq_table_column`	`uq_orders_number`
Foreign key	`fk_child_parent`	`fk_orders_customer`
Check	`chk_table_rule`	`chk_orders_quantity`

Cost

Constraints cost a little at write time (a lookup in an index to check a unique or foreign key, a small expression for a check). That cost is almost always worth paying, and unique and foreign keys come with indexes that speed up reads.

lightbulb

Start strict

It is easy to loosen a rule later (drop a constraint). It is hard to tighten a rule on a table that already contains bad rows. Put the constraints in from the first version of the table.