Indexes speed up search and JOIN operations. Constraints enforce data integrity, preventing bad data entry.

Constraints on Employees table:

-- Modify Employees table to add constraints
ALTER TABLE employees 
ADD CONSTRAINT chk_age CHECK (age > 18); -- Age must be greater than 18

ALTER TABLE employees 
ADD CONSTRAINT uq_name UNIQUE (name); -- Employee names must be unique

image

Constraint on Departments table:

-- Modify Departments table to add constraints
ALTER TABLE departments 
ADD CONSTRAINT uq_department_name UNIQUE (department_name); -- No duplicate department names

image

Adding indexes:

-- Create an index on the department_id column for faster joins
CREATE INDEX idx_department_id ON employees(department_id);

-- Create an index on the employee name for fast searches
CREATE INDEX idx_employee_name ON employees(name);

-- Create an index on the department name for quick lookups
CREATE INDEX idx_department_name ON departments(department_name);

image

Verification of usage of indexes:

EXPLAIN ANALYZE 
SELECT e.name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;

image

Testing the implementation of constraints: It fails correctly

-- This should fail due to CHECK constraint (age < 18)
INSERT INTO employees (id, name, age, department_id) VALUES (11, 'Tom', 16, 2);

-- This should fail due to UNIQUE constraint on employee name
INSERT INTO employees (id, name, age, department_id) VALUES (12, 'Alice', 30, 3);

image