SQL Constraints
Constraints are rules the database enforces on every insert and update. They keep bad data out — even when the app forgets to.
The six standard constraints
| Constraint | Rule |
|---|---|
NOT NULL | Column may never be NULL. |
UNIQUE | No two rows may share the value(s). |
PRIMARY KEY | UNIQUE + NOT NULL — identifies a row. |
FOREIGN KEY | Value must exist in another table. |
CHECK | Custom boolean condition. |
DEFAULT | Fills in a value when none is supplied. |
Inline at column level
SQL
CREATE TABLE orders ( id INT PRIMARY KEY, customer_id INT NOT NULL REFERENCES customers(id), total DECIMAL(10,2) NOT NULL CHECK (total >= 0), status VARCHAR(20) NOT NULL DEFAULT 'pending' );
Or named at table level
Naming constraints makes errors readable and lets you drop them later by name:
SQL
CREATE TABLE orders ( id INT, customer_id INT NOT NULL, total DECIMAL(10,2) NOT NULL, CONSTRAINT pk_orders PRIMARY KEY (id), CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers(id), CONSTRAINT chk_total_positive CHECK (total >= 0) );
Why use them?
- Bugs in one service can't corrupt data behind another's back.
- The schema documents the rules — no need to read app code.
- The planner uses them to choose better query plans.
Tip: Adding a CHECK constraint to an existing big table can be slow because the engine validates every existing row. Most DBs let you add it as
NOT VALID and validate later in a separate, online step.Example
Example
CREATE TABLE orders ( id INT PRIMARY KEY, customer_id INT NOT NULL, total DECIMAL(10,2) CHECK (total >= 0) );Try it Yourself »
Exercise
Name a constraint so you can drop it later.
fk_customer FOREIGN KEY ...
Ten letters; the naming keyword.
Discussion
Loading…