Constraints are rules enforced by the database on the data in tables — guaranteeing data integrity at the database level (not just in application code). The main ones: PRIMARY KEY, FOREIGN KEY, UNIQUE, , , and .
Constraints are rules enforced by the database on the data in tables — guaranteeing data integrity at the database level (not just in application code). The main ones: PRIMARY KEY, FOREIGN KEY, UNIQUE, , , and .
NOT NULLCHECKDEFAULTCREATE TABLE users (
id INT PRIMARY KEY, -- unique + not null identifier
email VARCHAR(255) UNIQUE NOT NULL, -- must be unique AND present
age INT CHECK (age >= 0 AND age <= 120), -- must satisfy a condition
country VARCHAR(2) DEFAULT 'US', -- default value if not provided
role VARCHAR(20) NOT NULL DEFAULT 'user',
manager_id INT REFERENCES users(id) -- FOREIGN KEY (referential integrity)
);
PRIMARY KEY → uniquely identifies a row (unique + not null, indexed)
FOREIGN KEY → references another table's key (referential integrity)
UNIQUE → no duplicate values allowed in this column
NOT NULL → the column must have a value (can't be NULL)
CHECK → the value must satisfy a condition (age >= 0, status IN (...))
DEFAULT → a value used when none is provided on insert
-- ❌ relying only on application code to enforce uniqueness:
-- a race condition or a bug or a different app could insert a duplicate
-- ✅ a UNIQUE constraint guarantees it at the DB level — ALWAYS enforced,
-- regardless of which code or how many apps access the database
email VARCHAR(255) UNIQUE
Database constraints are the last line of defense for data integrity — they're enforced no matter what code inserts data (multiple applications, scripts, manual changes, or buggy code), and they handle race conditions that application-level checks miss.
CHECK (price > 0) -- prices must be positive
CHECK (status IN ('active', 'inactive')) -- only valid statuses
CHECK (end_date > start_date) -- logical consistency
CHECK enforces business/domain rules directly in the schema.
Constraints are essential for maintaining data integrity — they guarantee that the data in your database satisfies rules (uniqueness, presence, valid values, valid references) at the database level, which is fundamental to reliable applications, so understanding them is important.
Knowing the constraint types (PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK, DEFAULT) and what each enforces is necessary for proper schema design.
The most important conceptual point is why enforce at the database level rather than only in application code: database constraints are the last line of defense for data integrity — they're always enforced regardless of which code or how many applications access the data, they catch bugs that application checks might miss, and crucially they handle race conditions that application-level validation can't (e.g. two simultaneous inserts both passing an app-level uniqueness check but a UNIQUE constraint correctly rejecting the duplicate).
Relying solely on application code for integrity is risky; constraints provide a guarantee.
Understanding CHECK constraints for enforcing domain/business rules (valid ranges, allowed values, logical consistency) directly in the schema is also valuable.
Since data integrity is critical for reliable applications, and since constraints provide database-level guarantees that protect against bugs, race conditions, and inconsistent data in ways application code alone cannot, understanding constraints — the types, their integrity guarantees, and the importance of database-level enforcement — is important, foundational knowledge for designing robust databases and a key aspect of building applications whose data stays consistent and valid.
A library of IT interview questions with detailed answers — from Junior to Senior.
Donate