Constraints are rules the database enforces on every row, so that bad data is rejected before it can ever be saved. They turn a table from a plain bucket of values into a schema that guarantees your data stays correct.
A constraint is a promise the database makes and keeps for you: a promise that a column is never empty, that a value is unique, that a number stays inside a sensible range, or that a child row can never point at a parent that does not exist. Because the engine checks these rules on every INSERT, UPDATE, and DELETE, the guarantee holds no matter which application, script, or analyst touches the data. This guide walks through each constraint type, shows the CREATE TABLE and ALTER TABLE ADD CONSTRAINT syntax, and explains referential integrity with a concrete two table example.
What this guide covers
Every major engine supports the same core set of constraints, and the standard SQL syntax is remarkably portable. The official references for the three most common systems are worth bookmarking: PostgreSQL constraints, MySQL constraints, and the SQL Server tables documentation. If you are still getting comfortable with schema syntax, our guide to DDL commands covers CREATE, ALTER, and DROP in depth.
1. NOT NULL and DEFAULT
The NOT NULL constraint enforces that a column must always hold a value. Any attempt to insert or update a row that leaves the column empty is rejected. It is the simplest and most common guard, and it prevents a huge class of downstream bugs where code assumes a value is present and then crashes on a surprise NULL.
The DEFAULT clause is a close companion. It is not strictly a constraint, but it supplies a value when the caller does not, which pairs naturally with NOT NULL: the column can be required and still convenient to insert into.
-- Define at table creation
CREATE TABLE customers (
id INT,
email VARCHAR(255) NOT NULL,
status VARCHAR(20) NOT NULL DEFAULT 'active',
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);To add or change these rules on an existing table, use ALTER TABLE. The exact keyword differs slightly between engines, so both common forms are shown.
-- PostgreSQL / SQL Server style
ALTER TABLE customers ALTER COLUMN email SET NOT NULL;
ALTER TABLE customers ALTER COLUMN status SET DEFAULT 'active';
-- MySQL style (restate the full column definition)
ALTER TABLE customers MODIFY email VARCHAR(255) NOT NULL;2. UNIQUE
A UNIQUE constraint guarantees that no two rows share the same value in a column or set of columns. It is what stops two accounts from registering the same email, or two products from claiming the same SKU. Unlike a primary key, a unique column may allow one NULL (behavior varies slightly by engine), and a table may have many unique constraints but only one primary key.
-- Inline, at creation
CREATE TABLE customers (
id INT,
email VARCHAR(255) NOT NULL UNIQUE
);
-- Add later, with an explicit name
ALTER TABLE customers
ADD CONSTRAINT uq_customers_email UNIQUE (email);A unique constraint can span several columns. The pair must be unique together even if each column repeats on its own. This is exactly how you say "a user may appear in a project only once".
ALTER TABLE project_members
ADD CONSTRAINT uq_member UNIQUE (project_id, user_id);3. PRIMARY KEY and composite keys
A PRIMARY KEY uniquely identifies every row in a table. It combines two guarantees in one: the values are UNIQUE and they are NOT NULL. Each table gets exactly one primary key, and it is the anchor that foreign keys point at. Most tables use a single numeric or UUID column, but the key can also be built from several columns.
-- Single column key
CREATE TABLE customers (
id INT PRIMARY KEY,
email VARCHAR(255) NOT NULL UNIQUE
);
-- Add to an existing table
ALTER TABLE customers
ADD CONSTRAINT pk_customers PRIMARY KEY (id);Composite (multi column) keys
A composite key uses two or more columns together as the identifier. It is the natural choice for join tables that link two entities, where the meaningful identity is the pair itself. In the example below, a single student_id can appear many times and a single course_id can appear many times, but each combination may appear only once.
CREATE TABLE enrollments (
student_id INT NOT NULL,
course_id INT NOT NULL,
enrolled_on DATE NOT NULL DEFAULT CURRENT_DATE,
PRIMARY KEY (student_id, course_id)
);Choosing a key: many teams prefer a surrogate key (a generated id) even when a natural key exists, because business values like email or SKU can change over time, and a primary key should be stable. See our primary key vs foreign key guide for how the two work together.
4. FOREIGN KEY and referential integrity
A FOREIGN KEY links a column in one table to the primary key of another, and it enforces referential integrity: the promise that a child row can never reference a parent that does not exist. This is the rule that keeps a relational database relational. Consider two tables, customers and orders, where every order belongs to exactly one customer.
CREATE TABLE customers (
id INT PRIMARY KEY,
name VARCHAR(120) NOT NULL
);
CREATE TABLE orders (
id INT PRIMARY KEY,
customer_id INT NOT NULL,
total DECIMAL(10,2) NOT NULL,
CONSTRAINT fk_orders_customer
FOREIGN KEY (customer_id) REFERENCES customers(id)
);With this in place, the engine rejects any order whose customer_id has no matching customer:
-- Customer 999 does not exist
INSERT INTO orders (id, customer_id, total)
VALUES (1, 999, 49.90);
-- ERROR: foreign key constraint fails. The insert is rejected.Referential integrity works in both directions. Deleting a customer who still has orders is also blocked by default, because doing so would orphan those order rows. What happens instead is controlled by the referential actions ON DELETE and ON UPDATE.
ON DELETE and ON UPDATE actions
| Action | What happens to child rows when the parent is deleted or its key changes |
|---|---|
RESTRICT / NO ACTION | Block the operation while any child row still references the parent. This is the safe default. |
CASCADE | Automatically delete the child rows (or update their key) to match the parent. |
SET NULL | Set the child foreign key column to NULL. The column must be nullable. |
SET DEFAULT | Set the child foreign key column back to its declared default value. |
-- Delete a customer -> delete their orders too.
-- Change a customer id -> the orders follow.
ALTER TABLE orders
ADD CONSTRAINT fk_orders_customer
FOREIGN KEY (customer_id) REFERENCES customers(id)
ON DELETE CASCADE
ON UPDATE CASCADE;Pick the action to match your domain. Use CASCADE when a child cannot exist without its parent, such as order line items under an order. Use SET NULL when the link is optional, for example an employee whose department is deleted. Reach for RESTRICT when a deletion should force a human to deal with the dependent records first. Understanding how these relationships are laid out is the heart of database normalization.
Watch out: ON DELETE CASCADE is powerful and quiet. Deleting one parent row can silently remove thousands of child rows across several tables. Confirm the blast radius before you enable it in production, and make sure the foreign key columns are indexed so cascades stay fast.
5. CHECK
A CHECK constraint enforces that every row satisfies a boolean condition. It is your tool for domain rules that the type system alone cannot express: a price that must not be negative, a discount between 0 and 100, an end date on or after a start date, or a status limited to a fixed set of words.
CREATE TABLE products (
id INT PRIMARY KEY,
price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
discount INT NOT NULL DEFAULT 0
CHECK (discount >= 0 AND discount <= 100),
status VARCHAR(20) NOT NULL
CHECK (status IN ('draft', 'active', 'retired'))
);
-- Add a named check to an existing table
ALTER TABLE products
ADD CONSTRAINT chk_price_positive CHECK (price >= 0);Two notes on portability. PostgreSQL and SQL Server have enforced CHECK constraints for years. MySQL only began enforcing them in version 8.0.16; older versions parsed the syntax but ignored it, so verify your server version. When a row fails a check, the whole statement is rejected, exactly like a foreign key violation.
6. Auto numbering: AUTO_INCREMENT, IDENTITY, SERIAL
Most primary keys are generated automatically so the application never has to invent an id. This is not a constraint in the strict sense, but it is where surrogate keys come from, and the syntax is one of the biggest differences between engines. The three dialects express the same idea in different words.
| Engine | Auto number syntax |
|---|---|
| MySQL | id INT AUTO_INCREMENT PRIMARY KEY |
| SQL Server | id INT IDENTITY(1,1) PRIMARY KEY |
| PostgreSQL (classic) | id SERIAL PRIMARY KEY |
| Standard SQL (portable) | id INT GENERATED ALWAYS AS IDENTITY |
-- PostgreSQL, standard and recommended form
CREATE TABLE invoices (
id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
amount DECIMAL(10,2) NOT NULL
);
-- MySQL
CREATE TABLE invoices (
id INT AUTO_INCREMENT PRIMARY KEY,
amount DECIMAL(10,2) NOT NULL
);If you are unsure which column type to reach for when defining these keys and columns, our reference on SQL data types covers integers, decimals, UUIDs, and more.
7. Naming constraints
If you do not name a constraint, the engine generates one for you, often something cryptic like orders_customer_id_fkey or SYS_C0012843. That is fine until you need to drop or alter it, at which point a clear, consistent name saves real time. Always name constraints you expect to manage later.
A widely used convention is a short prefix plus the table and column: pk_ for primary keys, fk_ for foreign keys, uq_ for unique, and chk_ for checks. Named constraints are trivial to remove or replace:
ALTER TABLE orders
ADD CONSTRAINT fk_orders_customer
FOREIGN KEY (customer_id) REFERENCES customers(id);
-- Later, drop it by that exact name
ALTER TABLE orders DROP CONSTRAINT fk_orders_customer;
-- MySQL uses DROP FOREIGN KEY for foreign keys specifically
ALTER TABLE orders DROP FOREIGN KEY fk_orders_customer;8. Summary table and where to enforce rules
Here is the whole toolkit at a glance. Keep it near your keyboard while you design a schema.
| Constraint | Enforces | Example |
|---|---|---|
NOT NULL | Column must have a value | email VARCHAR(255) NOT NULL |
DEFAULT | Supplies a value when none is given | status VARCHAR(20) DEFAULT 'active' |
UNIQUE | No duplicate values | UNIQUE (email) |
PRIMARY KEY | Unique and not null; row identity | PRIMARY KEY (id) |
FOREIGN KEY | Value must exist in a parent table | FOREIGN KEY (customer_id) REFERENCES customers(id) |
CHECK | Row must satisfy a condition | CHECK (price >= 0) |
The chart above is a representative illustration, not a formal study: it reflects how often each constraint tends to catch a data quality problem in typical application schemas. Foreign keys and NOT NULL rules do the heavy lifting because dangling references and missing values are two of the most common sources of corruption.
Enforce in the database or in application code?
A frequent debate is whether these rules belong in the database at all, given that the application already validates input. The strongest answer is: enforce them in both places, but treat the database as the source of truth.
Rule of thumb: application checks give friendly, early feedback to users, but they can be bypassed by another service, a migration script, a manual query, or a bug. Only a database constraint is guaranteed to hold for every writer, forever. Put integrity rules in the schema and let application validation be the convenient first line, not the last.
Databases enforce constraints atomically inside each transaction, which the application layer cannot fully replicate across concurrent writers without a lot of careful locking. That is why a unique index is the correct way to prevent duplicate emails, not a "check then insert" in code, which is open to a race condition. For a compact reference to all of this syntax, keep our SQL cheat sheet handy, and if you want a guided path from the ground up, the SQL beginner course builds these habits from day one.
Constraints are the cheapest insurance in your entire stack. A few lines of DDL at design time replace countless hours of cleanup, defensive code, and late night data fixes later. Declare what must be true, and let the engine keep that promise on every single row.