Introduction
Constraints are rules enforced by the database engine that guarantee data meets certain conditions, regardless of which application or user inserts it. Rather than relying on every application layer to validate data correctly, constraints push that validation down into the database itself, providing a single, reliable enforcement point. The main constraint types are PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK, and DEFAULT, each addressing a different class of data integrity rule, from uniqueness to referential validity to domain-specific business rules.
Cricket analogy: Match rules like only 11 players per side and a batsman can't be given out twice are enforced by the umpire regardless of which team argues otherwise, just as database constraints like PRIMARY KEY and CHECK enforce data rules regardless of which application writes the row.
Syntax
CREATE TABLE products (
product_id INT PRIMARY KEY,
sku VARCHAR(50) NOT NULL UNIQUE,
product_name VARCHAR(150) NOT NULL,
price DECIMAL(10,2) NOT NULL CHECK (price > 0),
stock_qty INT NOT NULL DEFAULT 0 CHECK (stock_qty >= 0),
category_id INT,
FOREIGN KEY (category_id) REFERENCES categories(category_id)
);Explanation
PRIMARY KEY guarantees uniqueness and non-nullability for the row identifier. FOREIGN KEY ensures category_id, if present, must match an existing row in categories, enforcing referential integrity. UNIQUE (applied to sku) allows at most one row per value but, unlike a primary key, can allow multiple NULLs in most databases and a table can have several UNIQUE constraints. NOT NULL simply forbids missing values in a column. CHECK enforces a custom boolean condition, such as price > 0 or stock_qty >= 0, rejecting any row that would violate the business rule. DEFAULT supplies an automatic value (0 for stock_qty) when no value is provided on INSERT, reducing the chance of NULLs where a sensible default exists.
Cricket analogy: PRIMARY KEY is like a player's unique jersey number that can never repeat or be blank on a team sheet; FOREIGN KEY ensures a listed bowling_team_id matches a real registered team; UNIQUE on a nickname allows only one player per nickname but permits several blank entries; CHECK enforces runs_scored >= 0; DEFAULT sets overs_bowled to 0 when unspecified.
Example
-- These inserts demonstrate each constraint being enforced
INSERT INTO products (product_id, sku, product_name, price)
VALUES (1, 'SKU-100', 'Wireless Mouse', 19.99); -- OK, stock_qty defaults to 0
INSERT INTO products (product_id, sku, product_name, price)
VALUES (2, 'SKU-100', 'Mechanical Keyboard', 59.99);
-- ERROR: duplicate value violates UNIQUE constraint on sku
INSERT INTO products (product_id, sku, product_name, price)
VALUES (3, 'SKU-101', 'USB Cable', -5.00);
-- ERROR: violates CHECK constraint (price > 0)
INSERT INTO products (product_id, sku, product_name, price)
VALUES (4, 'SKU-102', NULL, 9.99);
-- ERROR: violates NOT NULL constraint on product_nameOutput
Each failed INSERT is rejected before the row is ever written, so the products table can never contain a duplicate SKU, a negative price, or a missing product name — these guarantees hold no matter which application, script, or user issues the INSERT. This is why constraints are considered the last line of defense for data integrity: even if application-level validation has a bug or is bypassed entirely (e.g., a direct database migration script), the database itself will not allow invalid data to persist.
Cricket analogy: A scorer trying to log a negative run total or a duplicate jersey number gets rejected on the spot by the official scoring system before it ever reaches the record book, no matter which app or scorer's tablet submitted it, even a manual correction script can't sneak in bad data.
Key Takeaways
- PRIMARY KEY enforces uniqueness and non-nullability of the row identifier.
- FOREIGN KEY enforces referential integrity between related tables.
- UNIQUE prevents duplicate values in a column but permits NULLs; CHECK enforces custom business rules.
- NOT NULL and DEFAULT control missing-value behavior at the column level.
- Constraints enforce integrity at the database layer, independent of application code.
Practice what you learned
1. What is the key difference between a UNIQUE constraint and a PRIMARY KEY?
2. Which constraint would you use to ensure a 'quantity' column never contains a negative number?
3. What does a DEFAULT constraint do?
4. Why are database-level constraints considered more reliable than application-level validation alone?
Was this page helpful?
You May Also Like
Primary and Foreign Keys
Understand how primary keys uniquely identify rows and foreign keys enforce relationships between tables.
ACID Properties
The four guarantees—Atomicity, Consistency, Isolation, Durability—that make database transactions reliable.
Transactions and COMMIT/ROLLBACK
How to group SQL statements into transactions and use COMMIT and ROLLBACK to make or undo their effects.