Every CREATE TABLE statement in Module 7 already used PRIMARY KEY and FOREIGN KEY, two rules the database enforces automatically. Those are only 2 of 6 rule types a database can enforce on its own, without any application code having to remember to check first. This module covers all six properly, starting with what a constraint actually is.
Learning Objectives
- Define what a constraint is, and why the database enforcing it beats application code enforcing it.
- Name all six constraint types and what each one restricts.
- Write the same constraint as both a column-level and a table-level declaration.
INSERT or UPDATE
A new or changed row
Constraint Check
Every rule on that table
Accept
All rules satisfied
Reject
Any rule violated, the statement fails
What a Constraint Actually Is
A constraint is a rule attached to a column or table that the database itself enforces on every single INSERT or UPDATE, automatically, with no code required to check it first. Beacon Robotics' real system might have three different places that write to employees: the HR onboarding form, a bulk CSV import script, and this course's own SQL examples. A rule written only in one app's code ("don't let salary go negative") protects nothing the moment a second app, or a careless manual UPDATE, bypasses that code. A CHECK (salary > 0) constraint on the column itself protects every writer, forever, with zero chance of being forgotten.
This is the core distinction worth internalizing before the six types: validation in application code is advisory, the database doesn't know about it and won't enforce it. A constraint is mandatory, declared once on the table, enforced by the database engine on every write from every source.
The Six Types
| Constraint | What It Enforces |
|---|---|
| NOT NULL | This column can never store NULL, a value is required. |
| UNIQUE | Every value in this column (or combination of columns) must be distinct across all rows. |
| PRIMARY KEY | Uniquely identifies each row, combines NOT NULL and UNIQUE, only one per table. |
| FOREIGN KEY | A column's value must match an existing value in another table's column (or be NULL). |
| CHECK | A row-level expression must evaluate true, like salary > 0. |
| DEFAULT | Supplies a value automatically when an INSERT doesn't specify one. |
DEFAULT is a slightly different kind of rule than the other five, it fills in a missing value rather than rejecting an invalid one, but it's declared the same way and grouped with the others by every database and by this course.
Column-Level vs Table-Level Syntax
Both statements create an identical table. Column-level syntax is shorter and fine for a single-column rule. Table-level syntax is required the moment a constraint spans more than one column, project_assignments' composite PRIMARY KEY (employee_id, project_id), covered in the primary-key lesson in this module, can only be written table-level, there's no column to attach it to alone.
-- Column-level: the constraint sits right next to the column it applies to CREATE TABLE departments ( id INT PRIMARY KEY, name VARCHAR(50) NOT NULL ); -- Table-level: the same constraint, declared separately, by name CREATE TABLE departments ( id INT, name VARCHAR(50) NOT NULL, CONSTRAINT pk_departments PRIMARY KEY (id) );
Naming a Constraint
CONSTRAINT pk_departments in the example above names the constraint explicitly. Skip the name and the database generates one automatically, something like SYS_C0012345 on Oracle or a similarly opaque auto-generated name elsewhere, which works identically but is far harder to read in an error message or reference later in an ALTER TABLE ... DROP CONSTRAINT statement. Naming constraints deliberately, usually pk_/fk_/uq_/chk_ plus the table and column, is standard practice precisely so a future DROP or a constraint-violation error message is actually readable.
Advantages and Disadvantages
- + Enforced centrally: every writer to the table follows the same rule, with no reliance on every application remembering to check.
- + Self-documenting: a table's constraints describe its real business rules directly in the schema, readable without digging through application code.
- + Fails fast: bad data is rejected at write time, not discovered later during a report or an analysis query.
- − A constraint added after bad data already exists will fail to apply until that data is cleaned up first.
- − Overly strict constraints can reject legitimate edge-case data the schema's author didn't anticipate.
- − Each constraint adds a small amount of overhead to every INSERT and UPDATE, the check itself has to run.
Try It Yourself
Write a table-level, named PRIMARY KEY constraint for a table called projects, with an id column, on MySQL.
CREATE TABLE projects ( id INT, name VARCHAR(100), CONSTRAINT pk_projects PRIMARY KEY (id) );
Frequently Asked Questions
Can a column have more than one constraint at the same time?
Yes, routinely. employees.department_id is both a FOREIGN KEY and allowed to be NULL (no NOT NULL on it), while employees.id is both NOT NULL and UNIQUE as part of being the PRIMARY KEY. Constraints stack freely on the same column.
Do constraints slow down queries?
SELECT queries aren't affected, constraints only run checks on INSERT and UPDATE (and DELETE, for foreign keys with ON DELETE actions). A PRIMARY KEY or UNIQUE constraint actually speeds up SELECT queries that filter on it, since both create an index automatically, covered in the create-index lesson later in this module.
Common Beginner Mistakes
Believing application-level validation is enough on its own
It only protects data written through that one application. A second script, a manual fix via a database client, or a bug in that same validation code all bypass it completely. A real constraint on the column protects every writer, not just the one that remembered to check.
Trying to attach a composite constraint using column-level syntax
Column-level syntax attaches to exactly one column, there's nowhere to list a second column. Any constraint spanning more than one column, like project_assignments' composite primary key, must be written table-level.
Best Practices
- Name constraints explicitly (pk_, fk_, uq_, chk_ plus table/column) rather than letting the database auto-generate an opaque name.
- Default to enforcing real business rules as database constraints, not only as application-level validation.
- Use table-level syntax for anything spanning more than one column, it's the only form that works for those.
Interview Questions
Why is a database constraint generally more reliable than application-level validation?
A constraint is enforced by the database engine itself on every write, from every source, with no code to forget or bypass. Application validation only protects writes that go through that specific application's code path, a different script, a manual edit, or a bug in the validation logic all bypass it entirely.
Write a table-level CHECK constraint named chk_salary ensuring employees.salary is greater than 0, on PostgreSQL.
ALTER TABLE employees ADD CONSTRAINT chk_salary CHECK (salary > 0); — CONSTRAINT chk_salary names it explicitly so a future DROP CONSTRAINT chk_salary or an error message referencing it stays readable.
Summary
A constraint is a rule the database itself enforces on every write, not something left to application code to remember. The six types are NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, and DEFAULT, declared either column-level (simple, single-column) or table-level (required for anything spanning multiple columns), and ideally named explicitly either way.
What's Next?
NOT NULL first, the simplest of the six, and the one every other constraint in this module interacts with in some way.
Ready to Practice SQL?
Write and run real SQL queries online, right in your browser, no install or signup required. Pick a playground based on which database you want to practice on.
Both open a third-party SQL playground in a new tab. External site terms and privacy policy apply.