NOT NULL, UNIQUE, DEFAULT and CHECK

Let the database enforce the rules

Your application can validate input, but an application is not the only thing that writes to a database. Scripts, other services and people with a SQL client all do. Constraints are rules attached to the table itself, and MySQL checks them on every insert and update, so the data stays correct whatever sent it.

NOT NULL

A column marked NOT NULL must have a value. If it has no default, leaving it out of an insert is an error. Use it for everything that is genuinely required:

CREATE DATABASE IF NOT EXISTS practice;
USE practice;
DROP TABLE IF EXISTS accounts;
CREATE TABLE accounts (
  id       INT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(30) NOT NULL,
  email    VARCHAR(100) NOT NULL,
  nickname VARCHAR(30)
);

INSERT INTO accounts (username) VALUES ('ria');

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

Fields that really may be unknown (here nickname) are left nullable. Many bugs begin with columns that allow NULL without a good reason, so make NOT NULL your default choice.

UNIQUE

UNIQUE forbids two rows from having the same value in the column (or combination of columns). Add it to natural identifiers such as email addresses:

ALTER TABLE accounts ADD CONSTRAINT uq_accounts_email UNIQUE (email);
INSERT INTO accounts (username, email) VALUES ('ria', 'ria@example.com');

INSERT INTO accounts (username, email) VALUES ('ria2', 'ria@example.com');

ERROR 1062 (23000): Duplicate entry 'ria@example.com' for key 'accounts.uq_accounts_email'

The error names the constraint, which is why a good name pays off. A unique constraint also creates an index, so searching by that column is fast.

Unique over several columns means the combination must be unique. For example UNIQUE (username, email) allows the same username with different emails. A UNIQUE column that also allows NULL may contain many NULLs, because NULL is never equal to NULL.

DEFAULT

DEFAULT supplies a value when an insert leaves the column out. It can be a constant or, from MySQL 8.0.13, an expression in parentheses. Timestamps are the classic use:

DROP TABLE IF EXISTS tickets;
CREATE TABLE tickets (
  id         INT PRIMARY KEY AUTO_INCREMENT,
  title      VARCHAR(100) NOT NULL,
  status     VARCHAR(20) NOT NULL DEFAULT 'open',
  priority   TINYINT NOT NULL DEFAULT 3,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO tickets (title) VALUES ('Printer is broken');
INSERT INTO tickets (title, status) VALUES ('Reset password', 'closed');
SELECT id, title, status, priority FROM tickets;

+----+-------------------+--------+----------+
| id | title             | status | priority |
+----+-------------------+--------+----------+
|  1 | Printer is broken | open   |        3 |
|  2 | Reset password    | closed |        3 |
+----+-------------------+--------+----------+

The created_at value is filled by the server's clock, which is why it is left out of the result above. You can write DEFAULT explicitly in an insert to request the default for a column: VALUES ('x', DEFAULT).

CHECK

CHECK enforces a condition on the row. It is accepted syntax in all versions but actually enforced from MySQL 8.0.16; older versions parsed and ignored it.

DROP TABLE IF EXISTS items;
CREATE TABLE items (
  id    INT PRIMARY KEY AUTO_INCREMENT,
  name  VARCHAR(50) NOT NULL,
  price DECIMAL(8,2) NOT NULL,
  stock INT NOT NULL DEFAULT 0,
  CONSTRAINT chk_items_price CHECK (price >= 0),
  CONSTRAINT chk_items_stock CHECK (stock >= 0)
);
INSERT INTO items (name, price, stock) VALUES ('pen', 1.50, 100);

INSERT INTO items (name, price) VALUES ('free sample', -5.00);

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

UPDATE items SET stock = stock - 500 WHERE name = 'pen';

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

The check also protects updates, so stock cannot be driven below zero. A check can compare columns in the same row, for example CHECK (end_date >= start_date). It cannot look at other rows or other tables.

Seeing the constraints

SHOW CREATE TABLE items\G

*************************** 1. row ***************************
       Table: items
Create Table: CREATE TABLE `items` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `price` decimal(8,2) NOT NULL,
  `stock` int NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  CONSTRAINT `chk_items_price` CHECK ((`price` >= 0)),
  CONSTRAINT `chk_items_stock` CHECK ((`stock` >= 0))
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Constraints are part of the table definition. To change one later, use ALTER TABLE items DROP CHECK chk_items_price; (and DROP INDEX for a unique constraint), which the alter lesson covers.

Constraints and existing data

Adding a constraint to a table that already holds data will fail if some rows violate it. Fix or remove the bad rows first. Find them with a SELECT that uses the opposite condition, such as SELECT * FROM items WHERE price < 0.

Common mistakes

• Leaving columns nullable out of habit.
• Relying on application code alone for uniqueness. Two requests at once can both pass an application check. Only a UNIQUE constraint is race-proof.
• Assuming CHECK works on an old MySQL (before 8.0.16).
• Using DEFAULT to hide missing data, such as defaulting a price to 0.
• Forgetting that UNIQUE allows multiple NULLs.

Exercise

Create a table coupons with an auto-increment id, a required unique code (up to 20 characters), a percent_off integer that must be between 1 and 90, and an active flag that defaults to true. Show that an invalid percentage and a duplicate code are rejected.

DROP TABLE IF EXISTS coupons;
CREATE TABLE coupons (
  id          INT PRIMARY KEY AUTO_INCREMENT,
  code        VARCHAR(20) NOT NULL UNIQUE,
  percent_off TINYINT NOT NULL,
  active      BOOLEAN NOT NULL DEFAULT TRUE,
  CONSTRAINT chk_coupons_percent CHECK (percent_off BETWEEN 1 AND 90)
);
INSERT INTO coupons (code, percent_off) VALUES ('WELCOME10', 10);

INSERT INTO coupons (code, percent_off) VALUES ('BIG', 95);

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

INSERT INTO coupons (code, percent_off) VALUES ('WELCOME10', 20);

ERROR 1062 (23000): Duplicate entry 'WELCOME10' for key 'coupons.code'