Solve MySQL Foreign Key Constraint Errors Using ON DELETE CASCADE – Full PHP Example

Foreign key constraints are essential for relational integrity, but sometimes you may encounter errors when trying to delete parent rows if child rows exist. In this tutorial, we’ll solve this problem using ON DELETE CASCADE, with a real PHP + MySQL example, including registration/login system and database structure.

Problem Scenario

Suppose you have a database for a budget system:

bajeti_watumiaji (users table)
id jina_kamili baruapepe nenosiri tarehe_kujiunga
1 Faustine fau@gmail.com
hashedpassword 2025-01-01
bajeti_mapato (income table)
id mtumiaji_id kiasi tarehe
1 1 50000 2025-01-02

Problem:
If you try to delete a user:

DELETE FROM bajeti_watumiaji WHERE id = 1;


You get an error:

Cannot delete or update a parent row: a foreign key constraint fails...

Solution
1️⃣ Modify the foreign key to cascade deletes
ALTER TABLE bajeti_mapato
DROP FOREIGN KEY bajeti_mapato_ibfk_1,
ADD CONSTRAINT bajeti_mapato_ibfk_1
FOREIGN KEY (mtumiaji_id) REFERENCES bajeti_watumiaji(id) ON DELETE CASCADE;


ON DELETE CASCADE ensures that all related records in bajeti_mapato are automatically deleted when a user is deleted.

2️⃣ Example Database Creation
-- Users table
CREATE TABLE bajeti_watumiaji (
id INT AUTO_INCREMENT PRIMARY KEY,
jina_kamili VARCHAR(100) NOT NULL,
baruapepe VARCHAR(100) UNIQUE NOT NULL,
nenosiri VARCHAR(255) NOT NULL,
tarehe_kujiunga DATE NOT NULL
);

-- Income table
CREATE TABLE bajeti_mapato (
id INT AUTO_INCREMENT PRIMARY KEY,
mtumiaji_id INT NOT NULL,
kiasi DECIMAL(10,2) NOT NULL,
tarehe DATE NOT NULL,
FOREIGN KEY (mtumiaji_id) REFERENCES bajeti_watumiaji(id) ON DELETE CASCADE
);

3️⃣ PHP Example: Delete User Safely
<?php
$pdo = new PDO("mysql:host=localhost;dbname=u715272556_faulink;charset=utf8", "username", "password");

// Delete user with ID 1
$id = 1;
$stmt = $pdo->prepare("DELETE FROM bajeti_watumiaji WHERE id = ?");
$stmt->execute([$id]);

echo "User deleted successfully, including related income records!";
?>


✅ Now you can delete users without worrying about foreign key errors.

4️⃣ Example with PHP Login/Registration System

You can combine this with a PHP system where users log in before you delete them.

Registration + Login: 🔐 PHP Register/Login Code

Excel System Tutorial (for data export/import): 📘 Excel Systems

5️⃣ Pro Tips

Always backup your database before altering tables.

Test ON DELETE CASCADE in a staging environment first.

Use transactions in PHP if deleting multiple related rows:

$pdo->beginTransaction();
$stmt1 = $pdo->prepare("DELETE FROM bajeti_watumiaji WHERE id = ?");
$stmt1->execute([$id]);
$pdo->commit();

Tags:

MySQL, Foreign Key, ON DELETE CASCADE, PHP, Database, Budget System