Constraints Overview
A constraint is a rule attached to a table or column that the database enforces on every insert, update, and delete. Instead of trusting every application, script, or human that touches the database to always write correct data, a constraint makes incorrect data physically impossible to store — the database itself rejects the operation with an error.
Constraints are declared as part of CREATE TABLE (or added afterward with ALTER TABLE) and apply no matter which application, tool, or person is writing to the table.
The main constraint types
Constraint | What it enforces |
|---|---|
PRIMARY KEY | Uniquely identifies every row; implies NOT NULL and UNIQUE; at most one per table. |
FOREIGN KEY | A column’s value must match a value that exists in another table’s (usually primary) key — enforces referential integrity between tables. |
UNIQUE | No two rows may share the same value (or combination of values) in the constrained column(s); unlike PRIMARY KEY, multiple UNIQUE constraints are allowed and NULLs are generally permitted. |
NOT NULL | The column must always contain a value; NULL is rejected on insert or update. |
CHECK | A custom boolean expression that every row must satisfy, e.g. CHECK (price > 0). |
DEFAULT | Not a validation rule but a fallback value automatically applied when a column is omitted from an INSERT. |
CREATE TABLE products ( product_id INT PRIMARY KEY, sku VARCHAR(20) NOT NULL UNIQUE, name VARCHAR(100) NOT NULL, price DECIMAL(10, 2) NOT NULL CHECK (price > 0), category_id INT REFERENCES categories(category_id), in_stock BOOLEAN NOT NULL DEFAULT TRUE );
This single table definition already demonstrates every constraint type: product_id is the PRIMARY KEY, sku is UNIQUE and NOT NULL, price has a CHECK constraint, category_id is a FOREIGN KEY referencing another table, and in_stock has a DEFAULT value.
Why enforce rules in the database?
It is tempting to think validation belongs entirely in the application layer — a web form that checks a price is positive before submitting, or backend code that refuses to insert a row with a missing email. Constraints matter anyway, for a simple reason: the database is very often not accessed through just one application.
Multiple applications, internal scripts, ETL jobs, and admin tools may all write to the same tables — each would have to reimplement the same validation, and any one of them missing a check reopens the door to bad data.
Application-level bugs can bypass validation logic entirely; a database constraint cannot be accidentally skipped by a code path someone forgot to test.
Direct database access (a developer running an ad hoc UPDATE, a data migration script) has no application layer in front of it at all.
Constraints document the data model directly in the schema, so anyone reading the table definition understands the rules without having to trace through application code.