What Constraints Actually Do
Constraints are rules enforced by the database itself, not by application code — they guarantee data integrity even if a bug, a manual query, or a second application slips past your app's own validation logic. Relying on application-level validation alone is one of the most common data-integrity mistakes in growing systems; constraints are the last line of defense that can't be bypassed by a stray script or a forgotten edge case.
PRIMARY KEY
Uniquely identifies each row in a table. Implies both uniqueness and NOT NULL. A table can have only one primary key, though that key can span multiple columns (a composite key).
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(100) NOT NULL
);
-- Composite primary key: uniqueness enforced across the combination of columns
CREATE TABLE order_items (
order_id INT,
product_id INT,
quantity INT,
PRIMARY KEY (order_id, product_id)
);FOREIGN KEY
Enforces that a column's value must match an existing value in another table's primary (or unique) key — the mechanism that actually enforces relational integrity between tables, rather than just implying it through naming convention.
CREATE TABLE orders (
id INT PRIMARY KEY,
customer_id INT NOT NULL,
FOREIGN KEY (customer_id) REFERENCES customers(id)
);ON DELETE / ON UPDATE behavior controls what happens when the referenced row changes:
- CASCADE — automatically delete/update dependent rows too (e.g. deleting a customer deletes their orders)
- SET NULL — sets the foreign key column to NULL instead of deleting the dependent row (requires the column to be nullable)
- RESTRICT / NO ACTION — blocks the delete/update entirely if dependent rows exist (the safe default in most systems)
- SET DEFAULT — resets the foreign key to its default value
-- Deleting a customer automatically removes their orders too
FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE;
-- Deleting a customer is blocked while they still have orders — the safer default
FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE RESTRICT;CASCADE is powerful and dangerous — a single delete can ripple through many related tables. Use it deliberately for genuinely dependent data (order line items belonging to an order), and prefer RESTRICT or SET NULL when the relationship is looser (a customer's support tickets probably shouldn't vanish just because the customer record was deleted).
UNIQUE
Ensures no two rows share the same value in a column (or combination of columns), without implying NOT NULL the way a primary key does. A table can have multiple UNIQUE constraints, and most databases allow multiple NULLs in a UNIQUE column, since NULL is never considered equal to another NULL.
CREATE TABLE users (
id INT PRIMARY KEY,
email VARCHAR(255) UNIQUE NOT NULL,
username VARCHAR(50) UNIQUE
);
-- Composite UNIQUE: a student can only be enrolled once per course
CREATE TABLE enrollments (
student_id INT,
course_id INT,
UNIQUE (student_id, course_id)
);NOT NULL
The simplest and most underused constraint — forces a column to always have a value. Every column that shouldn't logically ever be empty should have this, and it's one of the cheapest ways to prevent an entire category of downstream bugs (NULL-handling errors, unexpected NULL propagation through calculations and joins).
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
hire_date DATE NOT NULL
);CHECK
Enforces an arbitrary boolean condition on a row's values — the most flexible constraint type, useful for business rules the database can guarantee without relying on application code.
CREATE TABLE products (
id INT PRIMARY KEY,
price DECIMAL(10,2) CHECK (price > 0),
discount_pct INT CHECK (discount_pct BETWEEN 0 AND 100)
);
-- Multi-column CHECK: end date must come after start date
CREATE TABLE bookings (
start_date DATE,
end_date DATE,
CHECK (end_date > start_date)
);Compatibility note: MySQL versions before 8.0.16 silently parsed but ignored CHECK constraints — always verify your MySQL version actually enforces them before relying on one for correctness in an older environment.
DEFAULT
Not a constraint in the strict sense, but closely related — supplies a value automatically when none is provided on insert, reducing the chance of accidentally-NULL columns.
CREATE TABLE orders (
id INT PRIMARY KEY,
status VARCHAR(20) NOT NULL DEFAULT 'pending',
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);Adding Constraints to an Existing Table
Constraints aren't only defined at table creation — they're commonly added later via ALTER TABLE, though adding one to a table with existing violating data will fail until that data is cleaned up first.
ALTER TABLE employees ADD CONSTRAINT chk_salary_positive CHECK (salary > 0);
ALTER TABLE users ADD CONSTRAINT uq_email UNIQUE (email);
ALTER TABLE orders ADD CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers(id);On large production tables, adding a constraint can require a full table scan to validate existing rows, which may briefly lock the table — PostgreSQL supports adding a foreign key as NOT VALID first (skipping the initial check) and validating it separately afterward, to avoid a long-held lock during peak traffic.
Common Mistakes
- Relying only on application-level validation instead of database constraints — a second application, a migration script, or direct database access can all bypass application code, but never a real constraint.
- Using ON DELETE CASCADE too broadly — turns what should be a contained delete into an unexpected chain reaction across unrelated tables.
- Forgetting that UNIQUE allows multiple NULLs — assuming a UNIQUE email column prevents "duplicate" NULL emails, when in fact most databases allow any number of NULLs in a UNIQUE column.
- Adding a NOT NULL or CHECK constraint to a large existing table without checking for violating data first — the ALTER TABLE will simply fail, sometimes after a long scan, rather than warning in advance.
Common Interview Questions
- What's the difference between PRIMARY KEY and UNIQUE? A table can have only one PRIMARY KEY (which implies NOT NULL); it can have multiple UNIQUE constraints, each allowing a NULL value.
- What does ON DELETE CASCADE do, and when is it risky? Automatically deletes dependent rows when the referenced row is deleted — risky when applied to loosely related data where an unintended chain of deletes could occur.
- Can a CHECK constraint reference multiple columns? Yes — e.g. enforcing that an end date is after a start date, as shown above.
- Why prefer database constraints over only validating in application code? Constraints are enforced universally, regardless of which application, script, or user touches the data — application validation can always be bypassed by something outside that specific code path.
Frequently Asked Questions
Do constraints slow down inserts and updates?
Slightly — each constraint adds a check the database must perform. In practice this cost is small compared to the cost of a real data-integrity bug reaching production, and indexed constraints (PRIMARY KEY, UNIQUE, FOREIGN KEY) are highly optimized in every major database.
Can you have more than one FOREIGN KEY per table?
Yes — a table can reference multiple other tables, each with its own FOREIGN KEY constraint, and even multiple foreign keys pointing to the same table for different relationships.
Practice Constraints in Real Schemas
Constraints are where SQL knowledge turns into real schema design skill. Try it hands-on with our SQL practice questions, or work through schema design scenarios in our case studies. For the data types constraints are commonly applied to, see our SQL data types cheat sheet.