Error
Error Code:
1834
MariaDB Error 1834: Parent Row Deletion Blocked
Description
This error occurs when you attempt to delete rows from a table that acts as the "parent" in a foreign key relationship, but there are still "child" rows in a related table referencing those parent rows. MariaDB prevents this deletion to maintain data integrity and avoid orphaned records, enforcing the defined foreign key constraint.
Error Message
Cannot delete rows from table which is parent in a foreign key constraint '%s' of table '%s'
Known Causes
4 known causesExisting Child Records
The parent row you are trying to delete is currently referenced by one or more rows in a child table, directly violating the foreign key constraint.
Incorrect Deletion Order
You are attempting to delete records from the parent table before deleting or disassociating the corresponding records in the child table.
Missing ON DELETE Action
The foreign key constraint was defined without an `ON DELETE CASCADE` or `ON DELETE SET NULL` action, or with `RESTRICT` implicitly, preventing the parent deletion.
Application Logic Error
The application code is attempting to delete parent records without first handling associated child records, leading to the database rejecting the operation.
Solutions
4 solutions available1. Temporarily Disable Foreign Key Checks easy
Quickly bypass the constraint to delete the parent row.
1
Begin a transaction to ensure the change is temporary and can be rolled back.
START TRANSACTION;2
Temporarily disable foreign key checks for the current session.
SET foreign_key_checks = 0;3
Execute the DELETE statement for the parent row. Replace `parent_table` and `parent_id_value` with your actual table and the ID of the row you want to delete.
DELETE FROM parent_table WHERE id = parent_id_value;4
Commit the transaction to make the deletion permanent.
COMMIT;5
Alternatively, if you don't want to commit, you can roll back the transaction to undo all changes made within it.
ROLLBACK;6
Note: Re-enable foreign key checks automatically occurs when the session ends. If you want to re-enable them immediately within a long-running session or script, use `SET foreign_key_checks = 1;` before committing or ending the session.
SET foreign_key_checks = 1;2. Delete Child Rows First medium
Remove dependent records before deleting the parent.
1
Identify the foreign key constraint and the child table involved. You can find this information by querying the `INFORMATION_SCHEMA.KEY_COLUMN_USAGE` table.
SELECT CONSTRAINT_NAME, TABLE_NAME, COLUMN_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_NAME = 'parent_table' AND TABLE_NAME = 'child_table';2
Delete the rows from the child table that reference the parent row you intend to delete. Replace `child_table`, `parent_id_column_in_child`, and `parent_id_value` with your specific details.
DELETE FROM child_table WHERE parent_id_column_in_child = parent_id_value;3
Once the child rows are deleted, you can safely delete the parent row. Replace `parent_table` and `parent_id_value`.
DELETE FROM parent_table WHERE id = parent_id_value;3. Modify Foreign Key Constraint to ON DELETE CASCADE advanced
Configure the constraint to automatically delete child rows.
1
Identify the foreign key constraint name. You can find this using `SHOW CREATE TABLE child_table;` and looking for the `CONSTRAINT ... FOREIGN KEY ... REFERENCES parent_table (...)` clause.
SHOW CREATE TABLE child_table;2
Drop the existing foreign key constraint. Replace `child_table` and `constraint_name`.
ALTER TABLE child_table DROP FOREIGN KEY constraint_name;3
Re-add the foreign key constraint with the `ON DELETE CASCADE` option. Replace `child_table`, `constraint_name`, `parent_id_column_in_child`, `parent_table`, and `parent_id_column_in_parent`.
ALTER TABLE child_table ADD CONSTRAINT constraint_name FOREIGN KEY (parent_id_column_in_child) REFERENCES parent_table (parent_id_column_in_parent) ON DELETE CASCADE;4
Now, when you delete a row from the `parent_table`, all corresponding rows in the `child_table` will be automatically deleted.
DELETE FROM parent_table WHERE id = parent_id_value;4. Modify Foreign Key Constraint to ON DELETE SET NULL advanced
Set the foreign key column in child rows to NULL when the parent is deleted.
1
Identify the foreign key constraint name. You can find this using `SHOW CREATE TABLE child_table;` and looking for the `CONSTRAINT ... FOREIGN KEY ... REFERENCES parent_table (...)` clause.
SHOW CREATE TABLE child_table;2
Ensure that the foreign key column in the child table is nullable. If it's not, you'll need to alter the column first.
ALTER TABLE child_table MODIFY COLUMN parent_id_column_in_child INT NULL;3
Drop the existing foreign key constraint. Replace `child_table` and `constraint_name`.
ALTER TABLE child_table DROP FOREIGN KEY constraint_name;4
Re-add the foreign key constraint with the `ON DELETE SET NULL` option. Replace `child_table`, `constraint_name`, `parent_id_column_in_child`, `parent_table`, and `parent_id_column_in_parent`.
ALTER TABLE child_table ADD CONSTRAINT constraint_name FOREIGN KEY (parent_id_column_in_child) REFERENCES parent_table (parent_id_column_in_parent) ON DELETE SET NULL;5
Now, when you delete a row from the `parent_table`, the `parent_id_column_in_child` in the corresponding `child_table` rows will be set to NULL.
DELETE FROM parent_table WHERE id = parent_id_value;