NOT NULL, DEFAULT, UNIQUE and CHECK

Concept

A constraint is a rule the database enforces on every write, from every client, forever. Application validation can be bypassed by a script, a migration or a second application; a constraint cannot.

Syntax

CREATE TABLE products (
    id       INT           NOT NULL,
    name     VARCHAR(80)   NOT NULL,
    sku      VARCHAR(20)   NOT NULL,
    category VARCHAR(40)   NOT NULL DEFAULT 'general',
    price    DECIMAL(10,2) NOT NULL,
    discount DECIMAL(5,2)  NULL,
    CONSTRAINT pk_products      PRIMARY KEY (id),
    CONSTRAINT uq_products_sku  UNIQUE (sku),
    CONSTRAINT ck_products_price CHECK (price > 0),
    CONSTRAINT ck_products_disc  CHECK (discount IS NULL OR (discount >= 0 AND discount <= 100))
);

Example

-- Each of these is rejected by a different constraint
INSERT INTO products (id, name, sku, price) VALUES (1, NULL, 'SKU1', 100);   -- NOT NULL
INSERT INTO products (id, name, sku, price) VALUES (2, 'Pen', 'SKU1', 100);  -- UNIQUE
INSERT INTO products (id, name, sku, price) VALUES (3, 'Pen', 'SKU3', -5);   -- CHECK

-- Omitting a defaulted column is fine
INSERT INTO products (id, name, sku, price) VALUES (4, 'Pen', 'SKU4', 100);
-- category is 'general'

Explanation

UNIQUE and NULL

-- Most engines allow MANY NULLs in a UNIQUE column
CREATE TABLE contacts (
    id    INT PRIMARY KEY,
    email VARCHAR(120) NULL,
    CONSTRAINT uq_contacts_email UNIQUE (email)
);

INSERT INTO contacts VALUES (1, NULL);
INSERT INTO contacts VALUES (2, NULL);   -- allowed in MySQL, PostgreSQL, Oracle, SQLite

Because NULL = NULL is unknown, two NULLs are not "the same value" and the constraint is not violated. SQL Server is the exception: its UNIQUE constraint permits exactly one NULL. If you need "at most one row without a value", add NOT NULL or a filtered index.

CHECK and NULL

A CHECK passes when the condition is TRUE or UNKNOWN. So CHECK (price > 0) happily accepts a NULL price. That is why the price column above is also declared NOT NULL - the two constraints do different jobs and are both needed.

DEFAULT

status     VARCHAR(10) NOT NULL DEFAULT 'active',
created_at DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP,
quantity   INT         NOT NULL DEFAULT 0

A default applies only when the column is omitted from the INSERT. Explicitly inserting NULL into a defaulted NOT NULL column is an error, not a fallback.

Important rules

• NOT NULL and CHECK are independent; a CHECK does not imply NOT NULL.
• UNIQUE usually permits multiple NULLs. SQL Server permits one.
• A UNIQUE constraint is backed by an index, so it also speeds up lookups on that column.
• A composite UNIQUE (a, b) constrains the combination, not each column separately.
• CHECK is enforced from MySQL 8.0.16 and MariaDB 10.2. Earlier MySQL versions parsed and silently ignored it.
• A CHECK can only reference columns of the same row - no subqueries, no other tables.

Common mistakes

• Expecting CHECK (salary > 0) to also prevent NULL.
• Adding UNIQUE to a column that already contains duplicates - clean the data first.
• Relying on application code alone, then discovering bad rows inserted by an import script.
• Writing DEFAULT and then explicitly passing NULL from the application layer.
• Assuming CHECK is enforced on an old MySQL 5.7 server.

Best practices

• Make NOT NULL the default choice; justify every nullable column.
• Name constraints so error messages identify the rule: ck_products_price.
• Pair CHECK with NOT NULL whenever a value is mandatory.
• Enforce rules in the database and validate in the application - the database for correctness, the application for a good error message.
• Use a CHECK or a lookup table for status columns instead of free text.

Practice

1. Add constraints to orders so the total can never be negative and the status must be one of four known values.
2. Explain why UNIQUE(email) on a nullable column still allows several employees with no email.
3. Write a composite UNIQUE that stops the same employee being assigned to the same project twice.