Constraints (NOT NULL / UNIQUE / DEFAULT)

-- NOT NULL: Prohibits NULL values.
column_name data_type NOT NULL

-- UNIQUE: Prohibits duplicate values in the column.
column_name data_type UNIQUE

-- DEFAULT: Sets the default value used when a value is omitted.
column_name data_type DEFAULT default_value

-- CHECK: 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)

-- Create a table with various constraints applied.
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)
);

-- Apply a UNIQUE constraint across multiple columns (composite unique).
CREATE TABLE schedule (
    room_id  INT  NOT NULL,
    date     DATE NOT NULL,
    slot     INT  NOT NULL,
    UNIQUE (room_id, date, slot)
);

-- Error when an INSERT violates a UNIQUE constraint (MySQL)
-- ERROR 1062 (23000): Duplicate entry 'tanaka@example.com' 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, Oracle, SQL Server, 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, Oracle, SQL Server, 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. In Oracle, constraint names are especially important for identifying errors.

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.