A database constraint is a rule declared in the schema that the database management system enforces on every insert, update and delete, rejecting any change that would leave the data in an invalid state. Constraints are one of the database fundamentals that turn a table from a passive container into a structure that guarantees data integrity, regardless of which application or user writes to it.
Typical constraints require a value to be present, unique, within a range or matching a row in another table. Because the DBMS checks them inside the transaction, they hold even under concurrent writes, which application-side validation alone cannot ensure.
Types of database constraints
The SQL standard defines a small set of declarative constraints that every major relational database supports in some form. Each one protects a different aspect of correctness, from the presence of a value to the relationships between tables.
| Constraint | Rule it enforces | Typical use |
|---|---|---|
| NOT NULL | The column must always have a value | Mandatory attributes such as an email |
| UNIQUE | No two rows share the same value set | Natural keys, usernames |
| PRIMARY KEY | Unique and not null, one per table | Row identity |
| FOREIGN KEY | Value must exist in a referenced key | Referential integrity between tables |
| CHECK | A Boolean expression must not be false | Ranges, allowed values, cross-column rules |
| DEFAULT | Supplies a value when none is given | Timestamps, status flags |
| EXCLUSION (PostgreSQL) | No two rows may overlap by an operator | Non-overlapping bookings |
Strictly speaking, DEFAULT is not a constraint because it never rejects data; it is listed among column constraints in SQL syntax, which is why many tools group them together. A composite key applies a primary key or unique constraint to several columns at once, so only the combination must be unique.
How the database enforces constraints
When a statement modifies rows, the DBMS evaluates the relevant constraints and aborts the statement with an error if any is violated. Unique and primary key constraints are usually enforced through an automatically created unique index, which makes the check fast and also speeds up lookups by that key. Foreign key checks read the referenced table and take locks that prevent the parent row from disappearing while the child is being written.
A detail that often surprises developers concerns NULL. A CHECK constraint fails only when its expression is false; an expression that evaluates to unknown because of a NULL passes the check. Similarly, most systems allow several NULLs in a UNIQUE column because NULL is not equal to NULL, although SQL Server traditionally allows only one and PostgreSQL 15 added the NULLS NOT DISTINCT option.
Constraints are normally checked immediately after each statement. The SQL standard also allows deferrable constraints, checked only at commit, which is useful when two rows reference each other or when a batch temporarily breaks a rule. PostgreSQL and Oracle support deferral for unique, primary key and foreign key constraints; MySQL does not.
Referential actions on foreign keys
A foreign key constraint defines not only which values are valid but also what happens when the referenced row changes. These referential actions are declared with ON DELETE and ON UPDATE and determine whether dependent rows block, follow or lose their link.
| Action | Effect on child rows | When to use it |
|---|---|---|
| NO ACTION (default) | Delete fails if children exist, checked at statement end | Most business relationships |
| RESTRICT | Delete fails immediately | Strict protection of parent rows |
| CASCADE | Children are deleted or updated too | Owned data, such as order lines |
| SET NULL | Child reference becomes NULL | Optional links, such as an assigned agent |
| SET DEFAULT | Child reference gets its default value | Reassignment to a placeholder row |
CASCADE deserves caution: deleting one customer row can silently remove thousands of dependent rows across several tables. It suits strict ownership relationships and is risky for shared reference data.
Constraints compared with other integrity mechanisms
Constraints are declarative: they state what must be true, and the database decides how to check it. Database triggers and stored procedures can enforce more complex rules procedurally, and application code can validate input before it reaches the database. Each layer has a role, but only constraints are both always on and concurrency-safe by design.
| Mechanism | Strength | Weakness |
|---|---|---|
| Declarative constraint | Always enforced, optimizer-aware | Limited to row or key rules |
| Trigger | Can express multi-row logic | Hidden behavior, harder to test |
| Stored procedure | Centralized write logic | Bypassed by direct writes |
| Application validation | Friendly errors, early feedback | Each client must repeat it |
Constraints also help performance. Query optimizers in several systems use trusted foreign keys and NOT NULL declarations to eliminate unnecessary joins and simplify plans, so declaring them is not merely a matter of correctness.
Practical guidance and common mistakes
A sound default is to declare every rule the data model implies: primary keys on all tables, NOT NULL on mandatory columns, foreign keys on every relationship and CHECK constraints for ranges and allowed values. Constraints should have explicit, readable names such as chk_order_quantity_positive, because the name appears in error messages and migration scripts.
- Adding a constraint to a large table may scan all rows and hold locks; PostgreSQL’s
NOT VALIDfollowed byVALIDATE CONSTRAINT, and similar options elsewhere, reduce the impact. - Disabling constraints for bulk loads is common, but re-enabling them without validation leaves untrusted data behind.
- Missing indexes on foreign key columns make deletes on the parent table slow, because each delete must search the child table.
- Relying only on the application lets scripts, imports and other services write invalid data unnoticed.
In everyday language
A database constraint is a house rule that the database itself enforces. For example, every customer must have an email address, no two accounts may share a username, and every order must belong to a real customer.
When any program tries to save data that breaks a rule, the database simply refuses. It works like a ticket gate: whoever arrives, the gate checks every ticket the same way, so no invalid entry slips through.
Seen in the real world
A clinic runs an appointment system used by a web portal, a mobile app and a nightly import from a partner laboratory. Early on, all validation lived in the web portal, and the import occasionally created appointments with a negative duration or for patients who did not exist. These bad rows broke the daily schedule report.
The team adds a foreign key from appointments to patients, a CHECK constraint requiring the end time to be later than the start time, and a NOT NULL constraint on the doctor column. Because the table already holds millions of rows, they add the constraints as not yet validated, clean up about two hundred invalid rows, and then validate them. From then on, the import receives a clear error for each invalid record, and the schedule report no longer fails, whichever client writes the data.