How to Temporarily Disable Foreign Key Constraints in MySQL?

Temporary Disablement of Foreign Key Constraints in MySQL

In MySQL, foreign key constraints enforce referential integrity, ensuring data consistency across related tables. However, there are scenarios where temporarily disabling these constraints is necessary, such as during model deletions.

Consider the following Django models with mutual foreign key relationships:

class Item(models.Model):
    style = models.ForeignKey('Style', on_delete=models.CASCADE)

class Style(models.Model):
    item = models.ForeignKey('Item', on_delete=models.CASCADE)

Attempting to delete an instance of both models sequentially may result in an error:

cursor.execute("DELETE FROM myapp_item WHERE n = %s", n)
transaction.commit_unless_managed()  # foreign key constraint fails here

cursor.execute("DELETE FROM myapp_style WHERE n = %s", n)
transaction.commit_unless_managed()

To bypass this foreign key constraint, you can temporarily disable it using one of the following methods:

1. DISABLE KEYS:

ALTER TABLE myapp_item DISABLE KEYS;

This command re-enables the constraints once the table is reopened or closed.

2. SET FOREIGN_KEY_CHECKS:

SET FOREIGN_KEY_CHECKS = 0;  # Disable foreign key checks

Remember to re-enable the constraints after the deletion operation:

SET FOREIGN_KEY_CHECKS = 1;  # Re-enable foreign key checks

The above is the detailed content of How to Temporarily Disable Foreign Key Constraints in MySQL?. For more information, please follow other related articles on the PHP Chinese website!