Table Constraints
Overview
Constraints are the bouncers at the door of your database. While data types (like INT) stop someone from inserting the word 'Apple' into a number column, Constraints enforce business logic. They ensure that an email is uniquely registered, that an age is never negative, and that a user ID cannot be left blank. Without strict constraints, bad data will silently pollute your database and eventually crash your production application.
Syntax
-- 1. PRIMARY KEY: Must be UNIQUE and NOT NULL. Identifies the row.
-- 2. NOT NULL: Forces the column to always have a value.
-- 3. UNIQUE: Ensures no two rows have the exact same value.
-- 4. CHECK: Enforces custom mathematical/logical rules.
-- 5. FOREIGN KEY: Enforces a link to another table.
CREATE TABLE employees (
emp_id INT PRIMARY KEY, -- 1. Rule: Unique & Not Null
email VARCHAR(100) NOT NULL UNIQUE, -- 2 & 3. Rule: Required & No duplicates
salary DECIMAL(10,2) CHECK (salary >= 30000), -- 4. Rule: Minimum wage enforcement
dept_id INT,
-- 5. Rule: The dept_id MUST exist in the 'departments' table!
CONSTRAINT fk_department FOREIGN KEY (dept_id) REFERENCES departments(id)
);Common Pitfalls
Relying entirely on frontend React validation (like an HTML
requiredattribute) instead of databaseNOT NULLconstraints. A hacker can easily bypass the frontend via an API call and injectNULLinto your database. The database constraints are your absolute last line of defense.Forgetting that
UNIQUEallows multipleNULLvalues. In most SQL dialects,NULLdoes not equalNULL(it equals 'Unknown'). Therefore, aUNIQUEconstraint will happily let you insert 50 rows where the email isNULL, because 'Unknown' is technically not a duplicate of 'Unknown'. You must combineUNIQUEwithNOT NULL.
Interview Questions
What is the structural difference between a PRIMARY KEY and a UNIQUE constraint?
A table can have multiple UNIQUE constraints (e.g., email, phone number, SSN), and UNIQUE columns can sometimes accept NULL values. A table can only have exactly ONE PRIMARY KEY, and it strictly cannot contain NULL values. The Primary Key is the definitive identifier for the row.
Real-World Example
Using a CHECK constraint to prevent catastrophic inventory bugs.
CREATE TABLE inventory (
product_id INT PRIMARY KEY,
stock_count INT NOT NULL,
-- This single line prevents the backend from ever accidentally
-- selling an item we don't have, forcing the transaction to fail!
CONSTRAINT prevent_oversell CHECK (stock_count >= 0)
);Check Your Knowledge
Test your understanding of Table Constraints with these quick questions.