Intermediate LevelSQL & MySQL
Why must Foreign Key columns be indexed in MySQL InnoDB?
❓ Interview Prompt: Explain Foreign Key indexing requirements in InnoDB, preventing full table locks on parent table DML operations, and cascading performance.
MySQL InnoDB requires all Foreign Key columns to be indexed:
Why FK Indexes Are Mandatory:
When a row in a parent table is deleted or updated, InnoDB must verify whether matching records exist in the child table:
•
With FK Index: InnoDB executes a fast B-Tree lookup on the child table.
•
Without FK Index: InnoDB would be forced to execute a full table scan on the child table, placing exclusive locks on the entire child table!
Code Implementation Example
sql
-- Adding an index on foreign key user_id column
CREATE TABLE orders (
id BIGINT PRIMARY KEY,
user_id BIGINT NOT NULL,
CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users(id),
INDEX idx_orders_user_id (user_id) -- Mandatory index for FK performance!
);💡Creating a mandatory secondary index on a Foreign Key column.
🎯 Interview Key Takeaway
Always index Foreign Key columns to prevent full table scans and lock contention during parent table updates.
#MySQL#Foreign Key#Indexes#InnoDB#Intermediate
Back to all SQL & MySQL Questions→