How do you disable and re-enable foreign key checks in MySQL? Why would you do that?

Difficulty: 4/10

foreign key constraints, session variables, data migration

Disabling and Re-Enabling Foreign Key Checks in MySQL

MySQL allows disabling foreign key checks temporarily, which is useful when performing bulk operations that would otherwise violate referential integrity.

1. Disable Foreign Key Checks

1
Disables enforcement of foreign key constraints.
2
Allows operations that would normally fail (e.g., deleting parent rows first, loading data out of order).

Command

2. Re-Enable Foreign Key Checks

1
Restores the enforcement of foreign key constraints.
2
MySQL will start validating new operations again.

Command

3. Why Disable Foreign Key Checks?

1
Bulk data import → Load data faster without constraint checks.
2
Dropping or truncating tables that are referenced by foreign keys.
3
Reordering inserts → Useful when parent rows are inserted after child rows during migration.
4
Changing schema (e.g., altering columns involved in foreign keys).

Foreign key checks should only be disabled temporarily. Leaving them off can break referential integrity and create invalid relationships.

Scenario Questions

0-2 years experience

Follow-up Questions

Difficulty: 4/10

foreign key constraints, session variables, data migration

Disabling and Re-Enabling Foreign Key Checks in MySQL

MySQL allows disabling foreign key checks temporarily, which is useful when performing bulk operations that would otherwise violate referential integrity.

1. Disable Foreign Key Checks

1
Disables enforcement of foreign key constraints.
2
Allows operations that would normally fail (e.g., deleting parent rows first, loading data out of order).

Command

2. Re-Enable Foreign Key Checks

1
Restores the enforcement of foreign key constraints.
2
MySQL will start validating new operations again.

Command

3. Why Disable Foreign Key Checks?

1
Bulk data import → Load data faster without constraint checks.
2
Dropping or truncating tables that are referenced by foreign keys.
3
Reordering inserts → Useful when parent rows are inserted after child rows during migration.
4
Changing schema (e.g., altering columns involved in foreign keys).

Foreign key checks should only be disabled temporarily. Leaving them off can break referential integrity and create invalid relationships.

Scenario Questions

0-2 years experience