MySQL 1221: Cannot delete parent row.

What is MySQL 1221: Cannot delete parent row.

When encountering the error 1221: Cannot delete parent row in MySQL DB, it indicates a foreign key constraint is preventing the row deletion due to its reference in a child table. Here are immediate actions to take:

Identify the Foreign Key Constraint and Referencing Table:

- Run the following query, replacing 'yourtablename' with the name of the table you are trying to delete from:SELECT CONSTRAINTNAME, TABLENAME, COLUMNNAME, REFERENCEDTABLENAME, REFERENCEDCOLUMN_NAME FROM INFORMATIONSCHEMA.KEYCOLUMN_USAGE WHERE TABLESCHEMA = DatabaseName AND TABLENAME = 'yourtablename' AND REFERENCEDTABLENAME IS NOT NULL; This query helps identify the foreign key constraint(s) and the referenced table(s) that are causing the issue.

Check the Dependent Rows in the Child Table:

- Based on the information from step 1, check for dependent rows in the child table. Replace 'childtablename', 'childcolumnname', and 'parent_value' with the relevant table name, column name, and the value you tried to delete, respectively:SELECT * FROM childtablename WHERE childcolumnname = 'parent_value'; - This step helps to understand why the deletion is being blocked.

Make an Informed Decision on How to Proceed:

- Depending on the business logic and necessity, you have a few options:a. Delete Dependent Rows: If it's safe to remove the dependent rows, you could run:DELETE FROM childtablename WHERE childcolumnname = 'parent_value'; Ensure this does not violate any application logic or data integrity.b. Update Foreign Keys in Child Rows: If the dependent rows should not be deleted, consider updating them to remove the dependency. This might involve setting the foreign key column to `NULL` or pointing it to a different parent row, if applicable:UPDATE childtablename SET childcolumnname = NULL WHERE childcolumnname = 'parent_value'; orUPDATE childtablename SET childcolumnname = 'newparentvalue' WHERE childcolumnname = 'parent_value';c. Temporarily Disable Foreign Key Checks: If you understand the implications and it's absolutely necessary (e.g., bulk cleanup operations), you can temporarily disable foreign key checks:SET FOREIGNKEYCHECKS=0; -- Your DELETE operation here SET FOREIGNKEYCHECKS=1; Be cautious as this can lead to referential integrity issues.Remember to replace `DatabaseName`, `yourtablename`, `childtablename`, `childcolumnname`, and `parent_value` with actual values relevant to your database and query. Always consider the implications of modifying or deleting data, especially in production environments.