Constraints that protect your data: foreign keys, UNIQUE, and CHECK. They belong in the schema, not only in application validation – the database is the last line of defence.
Why constraints beat app-only checks
Your PHP or Python code validates input today. Next month a report script, a migration, or a tired developer bypasses it. Constraints reject bad data at the source. They also document rules for anyone reading the schema.
PRIMARY KEY and UNIQUE
Primary key: one per table, not null, uniquely identifies a row. UNIQUE: no duplicate values in that column (or column group); unlike primary key, you can have several UNIQUE constraints and nullable columns (MySQL allows multiple NULLs in UNIQUE depending on version/engine – check if that matters to you).
CREATE TABLE users (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
email VARCHAR(255) NOT NULL UNIQUE,
username VARCHAR(50) NOT NULL UNIQUE
);
-- Composite unique: one review per user per product
CREATE TABLE reviews (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
user_id INT UNSIGNED NOT NULL,
product_id INT UNSIGNED NOT NULL,
rating TINYINT UNSIGNED NOT NULL,
UNIQUE KEY uq_user_product (user_id, product_id)
);NOT NULL and DEFAULT
NOT NULL stops missing required values. DEFAULT supplies a value when the insert omits the column. Together they stop half-empty rows that break reports.
CREATE TABLE orders (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
status ENUM('pending','paid','shipped') NOT NULL DEFAULT 'pending',
placed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);Foreign keys
Foreign keys enforce referential integrity: orders.customer_id must reference an existing customers.id (or NULL if allowed).
CREATE TABLE orders (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
customer_id INT UNSIGNED NOT NULL,
CONSTRAINT fk_orders_customer
FOREIGN KEY (customer_id) REFERENCES customers(id)
ON DELETE RESTRICT
ON UPDATE CASCADE
);ON DELETE / ON UPDATE options:
- RESTRICT / NO ACTION – block delete/update of parent if children exist (safest default mentally)
- CASCADE – delete/update children when parent changes (use deliberately)
- SET NULL – child FK becomes NULL when parent deleted (column must be nullable)
Index foreign key columns – MySQL requires it on the referencing table for performance.
CHECK constraints
MySQL 8.0.16+ enforces CHECK constraints properly. Use them for simple in-table rules:
CREATE TABLE products (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(200) NOT NULL,
price DECIMAL(10, 2) NOT NULL,
stock INT NOT NULL DEFAULT 0,
CONSTRAINT chk_price_positive CHECK (price >= 0),
CONSTRAINT chk_stock_non_negative CHECK (stock >= 0)
);
CREATE TABLE reviews (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
rating TINYINT UNSIGNED NOT NULL,
CONSTRAINT chk_rating_range CHECK (rating BETWEEN 1 AND 5)
);CHECK cannot reference other tables – that is what foreign keys are for. Complex cross-table rules stay in application code or triggers (sparingly).
Adding constraints to existing tables
-- Clean bad data first, or ALTER will fail
ALTER TABLE products
ADD CONSTRAINT chk_price_positive CHECK (price >= 0);
ALTER TABLE orders
ADD CONSTRAINT fk_orders_customer
FOREIGN KEY (customer_id) REFERENCES customers(id);Adding FKs to legacy tables with orphan rows fails until you fix or delete orphans. That pain is the point – you discover data you did not know was broken.
When constraints feel awkward
Some teams disable FKs for speed during bulk imports, then re-enable. Some sharded or multi-database setups cannot use FKs across shards. If you skip them, document why and enforce integrity in code – knowingly, not by accident.
Inspecting constraints
SELECT * FROM information_schema.table_constraints
WHERE table_schema = 'shop' AND table_name = 'orders';
SELECT * FROM information_schema.referential_constraints
WHERE constraint_schema = 'shop';The next tutorial covers views – named queries that simplify repeated SELECTs and hide complexity without duplicating data.