Keys, Constraints & Indexing

📄 MySQLLesson 9Intermediate

Constraints enforce schema integrity rules. Indexes speed up lookup operations by building structural binary trees referencing rows.

1 Adding Constraints & Indexes

SQL — Indexes and Alter commands

# Create index on specific column to speed up filtering
CREATE INDEX idx_users_email ON users(email);

# Add Foreign Key constraint to table mapping relation references
ALTER TABLE posts
ADD CONSTRAINT fk_posts_user
FOREIGN KEY (user_id) REFERENCES users(id)
ON DELETE CASCADE;

2 Code Challenge

Challenge: Alter the products table adding a unique constraint to the product code field, and create a composite index covering both category and price fields.