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
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.