Explain the difference between CASCADE, SET NULL, and RESTRICT for ON DELETE foreign key actions. When would you use each?

CASCADE: Automatically delete child rows when parent is deleted. Use for owned data where children are meaningless without parent. Example: Delete order → delete order items. SET NULL: Set foreign key to NULL when parent is deleted. Use for optional relationships where child can exist without parent. Example: Delete manager → set employee.manager_id to NULL. RESTRICT (default): Prevent deletion of parent if children exist. Use for protected data where you want to prevent accidental deletion. Example: Cannot delete customer if they have orders. The choice depends on business rules: CASCADE for data cleanup, SET NULL for optional refs, RESTRICT for data protection.

Why should you always create indexes on foreign key columns?

Foreign key columns should be indexed because: (1) When deleting a parent row, the database must check if any child rows reference it. Without an index, this requires a full table scan of the child table. With 10M rows, this takes 30+ seconds. With an index, it takes milliseconds. (2) Queries joining on foreign keys are common. Indexes make these joins fast. (3) The performance cost is minimal - indexes on foreign keys are small and rarely updated. The only time you might skip the index is if the foreign key is already part of another index (like a composite primary key).

What is the difference between application-level validation and database constraints? Why do you need both?

Application validation provides immediate user feedback and better UX. It checks data before sending to database. But it can be bypassed via direct SQL, bugs, API exploits, or batch imports. Database constraints are the last line of defense. They cannot be bypassed and are enforced for all writes. You need both: Application validation for user experience (show error immediately when email is invalid). Database constraints for data integrity (ensure email is unique even if application has a bug). Example: JavaScript validates email format, database UNIQUE constraint ensures no duplicates. This defense-in-depth approach prevents data corruption.

Design a database schema for a social media platform with users, posts, comments, and likes. Include appropriate constraints to ensure data integrity.

CREATE TABLE users (

user_id SERIAL PRIMARY KEY,

username VARCHAR(50) UNIQUE NOT NULL,

email VARCHAR(255) UNIQUE NOT NULL,

created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

CONSTRAINT chk_users_valid_email CHECK (email LIKE '%@%.%')

);

CREATE TABLE posts (

post_id SERIAL PRIMARY KEY,

user_id INT NOT NULL,

content TEXT NOT NULL,

created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

CONSTRAINT fk_posts_users FOREIGN KEY (user_id)

REFERENCES users(user_id) ON DELETE CASCADE,

CONSTRAINT chk_posts_content_length CHECK (LENGTH(content) <= 5000)

);

CREATE TABLE comments (

comment_id SERIAL PRIMARY KEY,

post_id INT NOT NULL,

user_id INT NOT NULL,

content TEXT NOT NULL,

created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

CONSTRAINT fk_comments_posts FOREIGN KEY (post_id)

REFERENCES posts(post_id) ON DELETE CASCADE,

CONSTRAINT fk_comments_users FOREIGN KEY (user_id)

REFERENCES users(user_id) ON DELETE CASCADE,

CONSTRAINT chk_comments_content_length CHECK (LENGTH(content) <= 1000)

);

CREATE TABLE likes (

like_id SERIAL PRIMARY KEY,

post_id INT NOT NULL,

user_id INT NOT NULL,

created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,