The earlier DDL lesson's CREATE TABLE examples used PRIMARY KEY to uniquely identify each row. Four more constraints enforce rules on the data itself — the database refuses any change that would break them, rather than trusting the application to check.
Ensures a column's value must already exist as a primary key in another table — the mechanism that actually connects related tables together.
CREATE TABLE orders (
id INT PRIMARY KEY,
customer_id INT,
FOREIGN KEY (customer_id) REFERENCES customers(id)
);
-- This INSERT fails if customer_id 99 doesn't exist in customers
INSERT INTO orders (id, customer_id) VALUES (1, 99);ON DELETE CASCADE).Common Mistake
Forgetting a foreign key doesn't cause an immediate error — it just silently allows "orphaned" rows that reference nothing, discovered much later as confusing bugs.
CREATE TABLE users (
id INT PRIMARY KEY,
email VARCHAR(255) UNIQUE
);
-- Fails on the second row — the email already exists
INSERT INTO users (id, email) VALUES (1, 'sam@example.com');
INSERT INTO users (id, email) VALUES (2, 'sam@example.com');CREATE TABLE users (
id INT PRIMARY KEY,
email VARCHAR(255) NOT NULL
);
-- Fails — email is required
INSERT INTO users (id) VALUES (1);Common Mistake
A column with no NOT NULL constraint silently accepts NULL — a very common source of "why is this field empty" bugs traced back to a missing constraint, not application logic.
Enforces any boolean condition on a column's value — the most flexible constraint.
CREATE TABLE products (
id INT PRIMARY KEY,
price DECIMAL(10,2) CHECK (price > 0),
quantity INT CHECK (quantity >= 0)
);
-- Fails — price must be positive
INSERT INTO products (id, price, quantity) VALUES (1, -10, 5);| Constraint | Enforces |
|---|---|
| PRIMARY KEY | Uniquely identifies each row, never NULL (covered in the earlier DDL lesson) |
| FOREIGN KEY | A value must exist as a primary key in another table |
| UNIQUE | No two rows share the same value in this column |
| NOT NULL | A value is required — cannot be left empty |
| CHECK | A custom condition the value must satisfy |