Why this matters
As a Data Analyst, you often clean, join, and analyze data from transactional databases. Constraints and keys are how databases keep data accurate so your queries reflect reality. You will:
- Trust joins because foreign keys guarantee related rows exist.
- Prevent duplicate records with PRIMARY KEY and UNIQUE.
- Catch bad values early with CHECK (e.g., price >= 0).
- Speed up lookups and reduce noisy errors in dashboards.
Who this is for and prerequisites
- Who: Aspiring or junior Data Analysts, anyone querying OLTP/OLAP sources.
- Prerequisites: Basic SELECT/INSERT/UPDATE; comfort reading CREATE TABLE.
Concept explained simply
Constraints are rules the database enforces. Keys are special constraints that identify or connect rows.
Primary Key (PK)
Identifies each row uniquely. One table has one PRIMARY KEY (single or composite columns). Implies NOT NULL and UNIQUE.
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
name VARCHAR(100) NOT NULL
);
Foreign Key (FK)
Connects a child row to a parent row. Ensures the referenced parent exists.
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT NOT NULL,
total NUMERIC(10,2) CHECK (total >= 0),
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
Optional actions: ON DELETE CASCADE (delete children with parent), ON DELETE RESTRICT/NO ACTION (block delete), ON DELETE SET NULL (nullify child reference).
UNIQUE
Prevents duplicate values in a column (or set of columns). You can have many UNIQUE constraints per table.
ALTER TABLE customers ADD CONSTRAINT uq_customers_email UNIQUE (email);
CHECK
Validates that a column or row meets a condition.
CREATE TABLE products (
product_id INT PRIMARY KEY,
price NUMERIC(10,2),
CHECK (price > 0)
);
Note: Exact syntax support varies slightly by database; conditions should be deterministic and simple.
Mental model
Think of a table as a spreadsheet with locks:
- PRIMARY KEY = Unique row ID lock.
- FOREIGN KEY = Safety tether between tables.
- UNIQUE = No duplicates lock for business identifiers (email, SKU).
- CHECK = Gatekeeper that only lets valid values in.
Syntax cheat sheet
-- In CREATE TABLE
CREATE TABLE t (
id INT PRIMARY KEY,
email VARCHAR(255) UNIQUE,
qty INT CHECK (qty >= 0),
parent_id INT,
FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE RESTRICT
);
-- Add later
ALTER TABLE t ADD CONSTRAINT uq_t_email UNIQUE (email);
ALTER TABLE t ADD CONSTRAINT ck_t_qty CHECK (qty >= 0);
ALTER TABLE t ADD CONSTRAINT fk_t_parent FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE;
-- Drop
ALTER TABLE t DROP CONSTRAINT fk_t_parent;
Worked examples
Example 1: Clean customer-orders design
- Create customers and orders
CREATE TABLE customers ( customer_id INT PRIMARY KEY, email VARCHAR(255) UNIQUE NOT NULL, country CHAR(2) CHECK (country IN ('US','UK','DE','FR')) ); CREATE TABLE orders ( order_id INT PRIMARY KEY, customer_id INT NOT NULL, total NUMERIC(10,2) CHECK (total >= 0), FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ); - Test constraints
INSERT INTO orders(order_id, customer_id, total) VALUES (1, 999, 50.00); -- should fail (FK) INSERT INTO customers(customer_id, email, country) VALUES (1, 'a@x.com', 'US'); INSERT INTO orders(order_id, customer_id, total) VALUES (1, 1, 50.00); -- ok INSERT INTO customers(customer_id, email, country) VALUES (2, 'a@x.com', 'US'); -- should fail (UNIQUE)
Example 2: Composite primary key for order items
One order cannot have duplicate product lines. Use a composite PK.
CREATE TABLE products (
product_id INT PRIMARY KEY,
sku VARCHAR(50) UNIQUE NOT NULL,
price NUMERIC(10,2) CHECK (price > 0)
);
CREATE TABLE order_items (
order_id INT NOT NULL,
product_id INT NOT NULL,
qty INT NOT NULL CHECK (qty > 0),
PRIMARY KEY (order_id, product_id),
FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE,
FOREIGN KEY (product_id) REFERENCES products(product_id)
);
-- Duplicate product on same order should fail
INSERT INTO order_items(order_id, product_id, qty) VALUES (1, 10, 1);
INSERT INTO order_items(order_id, product_id, qty) VALUES (1, 10, 2); -- fails (PK)
Example 3: Add constraints to existing tables
- Find violations first
-- Find duplicate emails SELECT email, COUNT(*) FROM customers GROUP BY email HAVING COUNT(*) > 1; -- Find negative totals SELECT order_id, total FROM orders WHERE total < 0; - Fix data
DELETE FROM orders WHERE total < 0; -- or UPDATE to valid values - Add constraints
ALTER TABLE customers ADD CONSTRAINT uq_customers_email UNIQUE (email); ALTER TABLE orders ADD CONSTRAINT ck_orders_total CHECK (total >= 0);
Example 4: Choosing ON DELETE actions
Suppose an order must delete its items with it.
ALTER TABLE order_items DROP CONSTRAINT order_items_order_id_fkey; -- name may vary
ALTER TABLE order_items ADD CONSTRAINT fk_items_order
FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE;
-- Deleting the parent removes children automatically
DELETE FROM orders WHERE order_id = 1; -- related order_items removed
Use CASCADE when children are meaningless without parent. Use RESTRICT/NO ACTION when child records must keep the parent alive.
Practical data-validation patterns
- Numeric ranges: CHECK (discount_percent BETWEEN 0 AND 100)
- Allowed codes: CHECK (status IN ('new','paid','shipped','canceled'))
- Dates: CHECK (delivered_at IS NULL OR delivered_at >= ordered_at)
- Cross-column: CHECK ((ship_country = 'US' AND state IS NOT NULL) OR ship_country <> 'US')
Exercises (hands-on)
These mirror the exercises below. Try writing the SQL before opening solutions.
Exercise 1: Customer/Order integrity
- Create customers and orders tables with PK, FK, UNIQUE email, and CHECK constraints.
- Insert a failing order (bad FK), then correct it.
- Attempt duplicate email to see UNIQUE in action.
Exercise 2: Add constraints to existing data
- Create products without constraints, insert some bad rows.
- Clean the data and then add UNIQUE(sku) and CHECK(price > 0).
Exercise 3: Composite PK and cascade
- Create order_items with PRIMARY KEY(order_id, product_id).
- Add FK to orders with ON DELETE CASCADE.
- Show that deleting an order removes its items.
Exercise checklist
- [ ] All tables created without errors
- [ ] FK insert fails as expected, then succeeds after fixing
- [ ] UNIQUE duplicate is blocked
- [ ] CHECK prevents invalid values
- [ ] CASCADE removes child rows on delete
Common mistakes and self-check
- Using a natural key with unstable values (e.g., email) as PK. Prefer surrogate PK and add UNIQUE on the natural key.
- Missing FK leads to orphan rows and broken joins.
- CHECK with overly strict rules that block valid edge cases (e.g., country lists missing new markets).
- Forgetting composite keys on junction tables (duplicates slip in).
- Adding constraints before cleaning data causes migration failures.
How to self-check quickly
- Search for potential violations before adding: SELECTs for duplicates, invalid ranges, and orphan references.
- Try an intentional invalid INSERT to ensure each constraint works.
- Run small deletes to confirm ON DELETE behavior matches expectations.
Mini challenge
Design tables for subscriptions: users, plans, subscriptions(user_id, plan_id, start_date, end_date, status). Add constraints:
- PRIMARY KEYs for each table; composite key if needed.
- UNIQUE plan code.
- FK: subscriptions.user_id → users.id, subscriptions.plan_id → plans.id.
- CHECK: end_date IS NULL OR end_date >= start_date.
- CHECK: status IN ('active','paused','canceled','expired').
Tip
Consider PRIMARY KEY (user_id, plan_id, start_date) on subscriptions if a user can subscribe to the same plan multiple times over time.
Practical projects
- Retail mini-DB: customers, orders, products, order_items. Implement all constraints. Import a small CSV and write queries to find violations pre-constraint, then fix them.
- HR mini-DB: employees, departments, assignments(employee_id, department_id, from_date, to_date). Use composite PK on assignments, FKs to employees and departments, and date-range CHECKs.
Learning path and next steps
- Next in SQL: Joins and normalization basics (3NF), then indexes for performance.
- Data migration skills: adding constraints safely to existing data.
- Analytics modeling: star schemas and dimension/fact keys.
Progress and test
The quick test is available to everyone. If you are logged in, your progress will be saved automatically.
Quick Test
Answer the short questions to check your understanding.