SQL Constraints & Indexes

SQL Constraints & Indexes Tutorial: Keys, Rules & Performance

📖 Introduction: Building Reliable, Fast Databases

So far, you've learned to create tables and manipulate data. But what stops someone from:

• Creating two employees with the same ID?
• Entering a negative salary?
• Deleting a customer who still has active orders?
• Searching through millions of rows without any speed optimization?

Constraints are the rules that protect your data quality. Indexes are the shortcuts that make queries lightning fast.

Analogy: Constraints are like the guardrails on a highway — they keep data from going off the road. Indexes are like the index at the back of a book — they help you find information instantly without reading every page.

🛠️ Setting Up Practice Data

sqlCopy
1CREATE DATABASE company_db;
2USE company_db;
3
4CREATE TABLE departments (
5    dept_id INT PRIMARY KEY AUTO_INCREMENT,
6    dept_name VARCHAR(50) NOT NULL
7);
8
9INSERT INTO departments (dept_name) VALUES ('IT'), ('HR'), ('Sales');

1️⃣ PRIMARY KEY — The Unique Identifier

Purpose: Uniquely identifies each row in a table. No duplicates allowed. Cannot be NULL.

Rules:

• Every table should have a primary key
• Only one primary key per table
• Can be single-column or composite (multiple columns)

Example 1: Single-Column Primary Key

sqlCopy
1CREATE TABLE employees (
2    emp_id INT PRIMARY KEY AUTO_INCREMENT,
3    emp_name VARCHAR(100) NOT NULL,
4    email VARCHAR(100),
5    salary DECIMAL(10,2)
6);

What happens:

• emp_id must be unique for every employee
• emp_id cannot be NULL
• AUTO_INCREMENT automatically generates the next number

Testing the constraint:

sqlCopy
1-- This works
2INSERT INTO employees (emp_name, email, salary) 
3VALUES ('Alice', 'alice@email.com', 75000);
4
5-- This also works (auto-generates emp_id = 2)
6INSERT INTO employees (emp_name, email, salary) 
7VALUES ('Bob', 'bob@email.com', 60000);
8
9-- This FAILS if you try to manually duplicate the ID
10INSERT INTO employees (emp_id, emp_name, email, salary) 
11VALUES (1, 'Charlie', 'charlie@email.com', 50000);
12-- ERROR: Duplicate entry '1' for key 'PRIMARY'

Example 2: Composite Primary Key

When a single column isn't enough to guarantee uniqueness, use multiple columns.

sqlCopy
1CREATE TABLE enrollment (
2    student_id INT,
3    course_id INT,
4    enrollment_date DATE,
5    PRIMARY KEY (student_id, course_id)
6);

A student can enroll in many courses, and a course can have many students. But the combination of student + course must be unique.

2️⃣ FOREIGN KEY — Linking Tables Together

Purpose: Enforces referential integrity between two tables. Ensures a value in one table matches a value in another.

Syntax:

sqlCopy
1FOREIGN KEY (column_name) REFERENCES parent_table(parent_column)
2[ON DELETE action]
3[ON UPDATE action]

Example 3: Creating a Table with a Foreign Key

sqlCopy
1CREATE TABLE orders (
2    order_id INT PRIMARY KEY AUTO_INCREMENT,
3    customer_id INT,
4    order_date DATE DEFAULT CURRENT_DATE,
5    total_amount DECIMAL(10,2) CHECK (total_amount > 0),
6    status VARCHAR(20) DEFAULT 'Pending',
7    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
8        ON DELETE CASCADE
9        ON UPDATE CASCADE
10);

What this means:

• Every customer_id in orders must exist in the customers table
• You cannot place an order for a non-existent customer

Testing the constraint:

sqlCopy
1-- If customer_id 999 doesn't exist:
2INSERT INTO orders (customer_id, total_amount) 
3VALUES (999, 100.00);
4-- ERROR: Cannot add or update a child row: foreign key constraint fails

Example 4: ON DELETE and ON UPDATE Actions

sqlCopy
1-- If a customer is deleted, all their orders are automatically deleted
2FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
3    ON DELETE CASCADE;
4
5-- If a customer ID is updated, the order's customer_id updates too
6    ON UPDATE CASCADE;

CASCADE Example:

sqlCopy
1-- Customer 1 has 3 orders
2DELETE FROM customers WHERE customer_id = 1;
3-- All 3 orders are automatically deleted. No orphaned records!

Warning: ON DELETE CASCADE is powerful but dangerous. If you delete a parent, children vanish instantly. Use SET NULL or RESTRICT if you want to prevent accidental mass deletions.

3️⃣ UNIQUE — No Duplicate Values

Purpose: Ensures all values in a column (or combination of columns) are different.

Difference from PRIMARY KEY:

• PRIMARY KEY = UNIQUE + NOT NULL (only one per table)
• UNIQUE allows one NULL value (in most databases)

Example 5: UNIQUE Constraint

sqlCopy
1CREATE TABLE users (
2    user_id INT PRIMARY KEY AUTO_INCREMENT,
3    username VARCHAR(50) UNIQUE NOT NULL,
4    email VARCHAR(100) UNIQUE,
5    password_hash VARCHAR(255)
6);

Testing:

sqlCopy
1INSERT INTO users (username, email) VALUES ('alice', 'alice@email.com');
2-- Works
3
4INSERT INTO users (username, email) VALUES ('alice', 'bob@email.com');
5-- ERROR: Duplicate entry 'alice' for key 'username'

Example 6: Adding UNIQUE to Existing Table

sqlCopy
1ALTER TABLE employees 
2ADD CONSTRAINT uq_email UNIQUE (email);

4️⃣ NOT NULL — Required Values

Purpose: Prevents a column from having empty (NULL) values.

sqlCopy
1CREATE TABLE products (
2    product_id INT PRIMARY KEY AUTO_INCREMENT,
3    product_name VARCHAR(200) NOT NULL,  -- Must have a name
4    description TEXT,                     -- Can be empty
5    price DECIMAL(10,2) NOT NULL           -- Must have a price
6);

Testing:

sqlCopy
1INSERT INTO products (product_name, price) VALUES ('Laptop', 999.99);
2-- Works
3
4INSERT INTO products (product_name, price) VALUES (NULL, 999.99);
5-- ERROR: Column 'product_name' cannot be null

5️⃣ CHECK — Custom Validation Rules

Purpose: Ensures values meet a specific condition.

Example 7: CHECK Constraint

sqlCopy
1CREATE TABLE employees (
2    emp_id INT PRIMARY KEY AUTO_INCREMENT,
3    emp_name VARCHAR(100) NOT NULL,
4    salary DECIMAL(10,2),
5    age INT,
6    CHECK (salary > 0),
7    CHECK (age >= 18 AND age <= 65)
8);

Adding CHECK to existing table:

sqlCopy
1ALTER TABLE employees 
2ADD CONSTRAINT chk_salary CHECK (salary > 0);

Testing:

sqlCopy
1INSERT INTO employees (emp_name, salary, age) 
2VALUES ('Alice', 50000, 25);
3-- Works
4
5INSERT INTO employees (emp_name, salary, age) 
6VALUES ('Bob', -1000, 25);
7-- ERROR: CHECK constraint violation (salary must be > 0)
8
9INSERT INTO employees (emp_name, salary, age) 
10VALUES ('Charlie', 50000, 16);
11-- ERROR: CHECK constraint violation (age must be >= 18)

Note: MySQL enforced CHECK constraints starting from version 8.0.16. In older versions, the syntax is accepted but not enforced.

6️⃣ DEFAULT — Auto-Fill Missing Values

Purpose: Automatically inserts a value when none is provided.

sqlCopy
1CREATE TABLE tasks (
2    task_id INT PRIMARY KEY AUTO_INCREMENT,
3    task_name VARCHAR(100) NOT NULL,
4    status VARCHAR(20) DEFAULT 'Pending',
5    priority INT DEFAULT 3,
6    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
7);

Testing:

sqlCopy
1INSERT INTO tasks (task_name) VALUES ('Fix login bug');
2-- Automatically sets: status='Pending', priority=3, created_at=now()

7️⃣ Indexes — Speeding Up Queries

Purpose: Indexes create a sorted lookup structure that dramatically speeds up SELECT queries (especially WHERE, JOIN, and ORDER BY).

Trade-off: Indexes speed up reads but slow down writes (INSERT, UPDATE, DELETE) because the index must be updated too.

Example 8: Create a Single-Column Index

sqlCopy
1CREATE INDEX idx_employee_name ON employees(name);

What happens:

• MySQL creates a sorted list of employee names pointing to their row locations
• Searching WHERE name = 'Alice' becomes almost instant, even with millions of rows

Before index: Full table scan (checks every row) After index: Direct lookup (like finding a name in a phone book)

Example 9: Create a Unique Index

sqlCopy
1CREATE UNIQUE INDEX idx_email ON employees(email);

This is identical to ALTER TABLE ... ADD CONSTRAINT UNIQUE (email). It enforces uniqueness and creates an index.

Example 10: Create a Composite Index

sqlCopy
1CREATE INDEX idx_name_dept ON employees(name, department_id);

When to use composite indexes:

• When you frequently filter by both columns together: WHERE name = 'Alice' AND department_id = 1
• Column order matters! Put the most selective column first.

Example 11: Drop an Index

sqlCopy
1DROP INDEX idx_employee_name ON employees;

📝 Complete Constraints & Indexes Reference

🚀 Hands-On Project: Optimize a Slow Database

Scenario

Your e-commerce database has grown to 1 million products and 5 million orders. Queries are getting slow. Your job is to add the right constraints and indexes.

Current Slow Schema

sqlCopy
1CREATE TABLE products_slow (
2    id INT,
3    name VARCHAR(200),
4    price DECIMAL(10,2),
5    category_id INT
6);
7
8CREATE TABLE orders_slow (
9    id INT,
10    customer_id INT,
11    product_id INT,
12    order_date DATE,
13    amount DECIMAL(10,2)
14);

Step 1: Add Primary Keys

sqlCopy
1ALTER TABLE products_slow 
2ADD PRIMARY KEY (id);
3
4ALTER TABLE orders_slow 
5ADD PRIMARY KEY (id);

Step 2: Add Foreign Keys for Integrity

sqlCopy
1ALTER TABLE orders_slow 
2ADD CONSTRAINT fk_orders_customer 
3FOREIGN KEY (customer_id) REFERENCES customers(customer_id);
4
5ALTER TABLE orders_slow 
6ADD CONSTRAINT fk_orders_product 
7FOREIGN KEY (product_id) REFERENCES products_slow(id);

Step 3: Add NOT NULL and CHECK Constraints

sqlCopy
1ALTER TABLE products_slow 
2MODIFY name VARCHAR(200) NOT NULL,
3ADD CONSTRAINT chk_price CHECK (price > 0);
4
5ALTER TABLE orders_slow 
6MODIFY amount DECIMAL(10,2) NOT NULL,
7ADD CONSTRAINT chk_amount CHECK (amount > 0);

Step 4: Create Indexes for Common Queries

sqlCopy
1-- Speed up: SELECT * FROM products WHERE name = 'Laptop'
2CREATE INDEX idx_product_name ON products_slow(name);
3
4-- Speed up: SELECT * FROM orders WHERE customer_id = 123
5CREATE INDEX idx_orders_customer ON orders_slow(customer_id);
6
7-- Speed up: SELECT * FROM orders WHERE product_id = 456
8CREATE INDEX idx_orders_product ON orders_slow(product_id);
9
10-- Speed up: SELECT * FROM orders WHERE order_date BETWEEN '2026-01-01' AND '2026-12-31'
11CREATE INDEX idx_orders_date ON orders_slow(order_date);
12
13-- Speed up: SELECT * FROM products WHERE category_id = 5 AND price < 100
14CREATE INDEX idx_products_cat_price ON products_slow(category_id, price);

Step 5: Verify the Optimizations

sqlCopy
1-- Check all indexes on a table
2SHOW INDEX FROM orders_slow;
3
4-- Check all constraints
5SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
6WHERE TABLE_NAME = 'orders_slow';

✅ Module 9 Summary

🎯 Practice Exercises

1. Create a students table with student_id as PRIMARY KEY and email as UNIQUE.
2. Create an enrollments table with a FOREIGN KEY to students.
3. Add a CHECK constraint to ensure gpa is between 0.0 and 4.0.
4. Create an index on the last_name column of your students table.
5. Create a composite index on (last_name, first_name).
6. Explain why you shouldn't create indexes on every single column.
7. Write the SQL to drop the chk_gpa constraint if it exists.

🎓 What's Next?

In Module 10, you'll learn Views, Triggers & Stored Procedures — how to create reusable query shortcuts, automate actions with triggers, and write stored procedures for complex business logic! 🚀