NOT NULL requires a column to always have a value. UNIQUE requires every value in a column (or combination of columns) to be distinct across the whole table. CHECK enforces an arbitrary boolean condition on a row. DEFAULT supplies a value automatically when none is given on insert. Unlike validation written in application code, these are enforced by the database engine itself on every write, from any source.
NOT NULL and UNIQUE
UNIQUE can span multiple columns together — the combination must be unique, even if individual columns repeat.
CREATE TABLE users (
id INTEGER PRIMARY KEY,
email VARCHAR(255) NOT NULL UNIQUE,
username VARCHAR(50) NOT NULL
);
CREATE TABLE enrollments (
student_id INTEGER,
course_id INTEGER,
UNIQUE (student_id, course_id) -- a student can't enroll in the same course twice
);CHECK: arbitrary row-level rules
CHECK enforces any boolean expression on a row, rejecting inserts or updates that would violate it — a way to encode business rules directly in the schema rather than trusting every calling application to remember them.
CREATE TABLE products (
id INTEGER PRIMARY KEY,
price NUMERIC(10, 2) CHECK (price >= 0),
discount_percent INTEGER CHECK (discount_percent BETWEEN 0 AND 100)
);
INSERT INTO products (price) VALUES (-5.00);
-- ERROR: violates check constraint — price must be >= 0DEFAULT: a value when none is given
DEFAULT only applies when a column is omitted entirely from the INSERT — explicitly inserting NULL bypasses it (unless the column is also NOT NULL, in which case that explicit NULL fails).
CREATE TABLE posts (
id INTEGER PRIMARY KEY,
status VARCHAR(20) DEFAULT 'draft',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO posts (id) VALUES (1);
-- status becomes 'draft', created_at becomes the current time — both automatic