How to fix ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails in MySQL

First, find which foreign key constraint is blocking your operation:

SHOW CREATE TABLE child_table_name;

Look for lines like:

CONSTRAINT `fk_name` FOREIGN KEY (`parent_id`) REFERENCES `parent_table` (`id`)

Note the constraint name (e.g., fk_name) and the child table column and parent table reference.

Before deleting the parent, remove all child records that reference it:

DELETE FROM child_table WHERE parent_id = 123;
DELETE FROM parent_table WHERE id = 123;

Replace 123 with the actual parent row ID and adjust column names. This is the safest approach as it maintains explicit control over data deletion.

Pro tip: Use transactions to ensure both operations complete together:

START TRANSACTION;
DELETE FROM child_table WHERE parent_id = 123;
DELETE FROM parent_table WHERE id = 123;
COMMIT;

For future deletions, modify the foreign key to automatically delete child rows:

ALTER TABLE child_table
DROP FOREIGN KEY old_constraint_name,
ADD CONSTRAINT new_constraint_name
FOREIGN KEY (parent_id)
REFERENCES parent_table(id)
ON DELETE CASCADE
ON UPDATE CASCADE;

Now deleting a parent row will automatically delete all its child rows. Use this only if cascading deletes match your business logic.

Warning: Ensure cascading deletes won't accidentally remove important data.

If the child table's foreign key column allows NULL values, you can set it to NULL instead of deleting:

ALTER TABLE child_table
DROP FOREIGN KEY old_constraint_name,
ADD CONSTRAINT new_constraint_name
FOREIGN KEY (parent_id)
REFERENCES parent_table(id)
ON DELETE SET NULL
ON UPDATE SET NULL;

Now deleting a parent row will set child rows' parent_id to NULL instead of failing. This preserves the child records but removes the relationship.

Ensure parent and child columns have identical data types and collations:

DESC parent_table;  -- Check parent table schema
DESC child_table;   -- Check child table schema

Verify that the parent ID column and child foreign key column have the same:
- Data type (both INT, both BIGINT, etc.)
- Character set (if string-based)
- Collation (utf8mb4_unicode_ci, utf8mb4_general_ci, etc.)

If they don't match, alter the child column:

ALTER TABLE child_table
MODIFY parent_id INT NOT NULL;  -- Match parent's data type

Before deleting, find all child rows referencing a parent:

SELECT * FROM child_table WHERE parent_id = 123;

Or use INFORMATION_SCHEMA to find all child tables:

SELECT
  TABLE_NAME,
  COLUMN_NAME,
  REFERENCED_TABLE_NAME,
  REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE REFERENCED_TABLE_NAME = 'parent_table'
AND REFERENCED_COLUMN_NAME = 'id';

This shows all child tables and columns that reference your parent table.

As a last resort for data migrations, temporarily disable foreign key checks:

SET FOREIGN_KEY_CHECKS = 0;
DELETE FROM parent_table WHERE id = 123;
SET FOREIGN_KEY_CHECKS = 1;

CRITICAL WARNING: This is dangerous and should only be used:
- In development/staging environments
- When you control the entire migration script
- When you guarantee referential integrity manually
- NEVER in production without extreme caution

Disabling checks allows orphaned records, making your database inconsistent.