Learn how to fix MySQL Error #1451 "Cannot delete or update a parent row: a foreign key constraint fails". This step-by-step guide explains the cause and provides SQL solutions using DELETE, UPDATE, and ON DELETE CASCADE. Tutorial by Faulink.
Introduction
When working with MySQL databases, especially in systems that contain multiple related tables, you may encounter the following error:
#1451 - Cannot delete or update a parent row: a foreign key constraint fails

This error usually happens when you try to delete a record from a parent table that is still being referenced in another table through a foreign key relationship.

In this guide, you will learn:
Why the error occurs
How to identify the tables causing the problem
Different ways to fix it safely
The best professional solution for developers

This tutorial is prepared by Faulink Developers to help beginners and professionals manage database relationships properly.
Example of the Error

For example, you might see an error like this:

#1451 - Cannot delete or update a parent row: a foreign key constraint fails
(`database`.`teacher_subject_assignments`,
CONSTRAINT `teacher_subject_assignments_ibfk_1`
FOREIGN KEY (`teacher_id`) REFERENCES `users` (`id`))

This means:

You tried to delete a user from the users table

But the teacher_subject_assignments table still references that user

So MySQL blocks the deletion to protect data integrity.

Solution 1: Delete Child Records First

You must delete the related records before deleting the user.

Step 1

Delete records from the related table:

DELETE FROM teacher_subject_assignments
WHERE teacher_id = 5;
Step 2

Then delete the user:

DELETE FROM users
WHERE id = 5;
Solution 2: Use ON DELETE CASCADE (Recommended)

A professional way to solve this problem permanently is to allow MySQL to delete related records automatically.

Step 1: Remove the existing foreign key
ALTER TABLE teacher_subject_assignments
DROP FOREIGN KEY teacher_subject_assignments_ibfk_1;
Step 2: Add a new foreign key with CASCADE
ALTER TABLE teacher_subject_assignments
ADD CONSTRAINT teacher_subject_assignments_ibfk_1
FOREIGN KEY (teacher_id) REFERENCES users(id)
ON DELETE CASCADE
ON UPDATE CASCADE;

Now when you delete a user:

DELETE FROM users WHERE id = 5;

MySQL will automatically delete related records in teacher_subject_assignments.

How to See All Tables Referencing Users

If your database has many tables, you can check all foreign keys referencing the users table:

SELECT
TABLE_NAME,
COLUMN_NAME,
CONSTRAINT_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE REFERENCED_TABLE_NAME = 'users';

This helps you identify all relationships that may prevent deleting a user.

Best Practice for Developers

Professional developers usually use:

ON DELETE CASCADE

because it:

prevents database errors

keeps data clean

saves time managing related records

simplifies database management

However, always ensure you understand the relationships before enabling cascade deletes.

Conclusion

MySQL Error #1451 Cannot delete or update a parent row occurs because a record is still referenced by another table.

You can fix it by:

Deleting child records first

Updating references to another user

Using ON DELETE CASCADE to automate deletion

Understanding foreign key relationships is essential for building reliable and scalable database systems.

Learn More

For more database tutorials, PHP systems, and developer resources, visit:

👉 https://faulink.com

Faulink provides tutorials on:

PHP development

MySQL database management

Web system development

Programming tips for beginners