Constraints (NOT NULL / UNIQUE / DEFAULT)

Prohibits NULL values.

column_name data_type NOT NULL

Prohibits duplicate values in the column.

column_name data_type UNIQUE

Sets the default value used when a value is omitted.

column_name data_type DEFAULT default_value

Restricts the values that can be inserted (MySQL 8.0.16 and later).

column_name data_type CHECK (condition)

Named constraint (table-level).

CONSTRAINT constraint_name CHECK (condition)

The following examples create an employees table like the one shown below.

Creates a table with various constraints applied.

Note: The chk_age CHECK constraint skips validation when age is NULL, and only checks that the value is between 18 and 100 when a value is provided.

sample_constraints.sql

CREATE TABLE employees (
    employee_id INT           NOT NULL AUTO_INCREMENT,
    name        VARCHAR(100)  NOT NULL,
    email       VARCHAR(255)  NOT NULL UNIQUE,
    department  VARCHAR(50)   NOT NULL DEFAULT 'Unassigned',
    salary      INT           NOT NULL DEFAULT 0,
    age         INT,
    PRIMARY KEY (employee_id),
    CONSTRAINT chk_salary CHECK (salary >= 0),
    CONSTRAINT chk_age    CHECK (age IS NULL OR age BETWEEN 18 AND 100)
);

Query OK, 0 rows affected

Verifies the table structure. The constraints and default values set on each column are reflected.

sample_constraints.sql

DESCRIBE employees;

+-------------+--------------+------+-----+------------+----------------+
| Field       | Type         | Null | Key | Default    | Extra          |
+-------------+--------------+------+-----+------------+----------------+
| employee_id | int          | NO   | PRI | NULL       | auto_increment |
| name        | varchar(100) | NO   |     | NULL       |                |
| email       | varchar(255) | NO   | UNI | NULL       |                |
| department  | varchar(50)  | NO   |     | Unassigned |                |
| salary      | int          | NO   |     |          0 |                |
| age         | int          | YES  |     | NULL       |                |
+-------------+--------------+------+-----+------------+----------------+
6 rows in set

Applies a UNIQUE constraint across multiple columns (composite unique).

sample_constraints.sql

CREATE TABLE schedule (
    room_id  INT  NOT NULL,
    date     DATE NOT NULL,
    slot     INT  NOT NULL,
    UNIQUE (room_id, date, slot)
);

Query OK, 0 rows affected

INSERT INTO employees (name, email, department, salary, age)
VALUES ('user_a', 'rintaro@wp-p.info', 'org_a', 250000, 24);

Query OK, 1 row affected (0.01 sec)

SELECT * FROM employees;

+-------------+--------+-------------------+------------+--------+-----+
| employee_id | name   | email             | department | salary | age |
+-------------+--------+-------------------+------------+--------+-----+
|           1 | user_a | rintaro@wp-p.info | org_a      | 250000 |  24 |
+-------------+--------+-------------------+------------+--------+-----+
1 row in set

Error when an INSERT violates a UNIQUE constraint (MySQL).

-- ERROR 1062 (23000): Duplicate entry 'rintaro@wp-p.info' for key 'employees.email'

Error when an INSERT violates a CHECK constraint (MySQL 8.0.16 and later).

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

NOT NULL, UNIQUE, and DEFAULT are supported across all major databases.

CREATE TABLE employees (
    employee_id INT          NOT NULL,
    name        VARCHAR(100) NOT NULL,
    email       VARCHAR(255) NOT NULL UNIQUE,
    department  VARCHAR(50)  NOT NULL DEFAULT 'Unassigned',
    salary      INT          NOT NULL DEFAULT 0
);

The CHECK constraint has been supported in PostgreSQL and SQLite for a long time, but MySQL only added support in version 8.0.16. In earlier versions of MySQL, the syntax is accepted without error but the constraint is silently ignored.

-- MySQL 8.0.16 and later, PostgreSQL, SQLite (common syntax)
CREATE TABLE employees (
    salary INT NOT NULL,
    age    INT,
    CONSTRAINT chk_salary CHECK (salary >= 0),
    CONSTRAINT chk_age    CHECK (age IS NULL OR age BETWEEN 18 AND 100)
);

The CONSTRAINT name syntax for naming constraints is also common across databases, but the format of error messages varies by database.

Constraints are a mechanism for guaranteeing data accuracy at the database level. Using constraints together with application-side validation provides a double layer of protection, significantly reducing the risk of unexpected data entering the system.

Combining NOT NULL and DEFAULT lets you allow a value to be omitted while still prohibiting NULL. For example, score INT NOT NULL DEFAULT 0 automatically sets score to 0 when it is omitted.

For primary keys and foreign keys, see PRIMARY KEY / FOREIGN KEY. For the basics of creating tables, see CREATE TABLE.