I've recently stumbled upon this issue where I wanted to add ON DELETE CASCADE to every foreign key i had on my tables.
I couldn't afford to do this manually because the project I am working on had over 106 tables and it's still expanding, so the main idea here is to write a general script that can be applied to any project.
1- You need to fetch every foreign key for you DATABASE
SELECT TABLE_NAME,
COLUMN_NAME,
CONSTRAINT_NAME,
REFERENCED_TABLE_NAME,
REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = "<DATABASE_NAME>"
AND REFERENCED_COLUMN_NAME IS NOT NULL
AND CONSTRAINT_NAME LIKE "%_foreign"
Note I added CONSTRAINT_NAME LIKE "%_foreign" because when Laravel creates a foreign key it follows this convention below
CONVENTION:
Schema::table('student_grades', function(Blueprint $table){
// adding a foreign key
$table->foreign("student_id")->references("id")->on("students");
});
The created foreign key will be named student_grades_student_id_foreign.
hence the condition CONSTRAINT_NAME LIKE "%_foreign".
If you apply the previous SQL Query you will get data like this
2- Re-create your foreign key
- make a migration file
php artisan make:migration alter_foreign_keys_on_tables
Now let's get to the code
public function up() {
// The SQL Query
$data = DB::table("INFORMATION_SCHEMA.KEY_COLUMN_USAGE")
->select([
"TABLE_NAME",
"COLUMN_NAME",
"CONSTRAINT_NAME",
"REFERENCED_TABLE_NAME",
"REFERENCED_COLUMN_NAME"
])
->whereRaw("TABLE_SCHEMA = '<DATABASE_NAME>'
AND REFERENCED_COLUMN_NAME IS NOT NULL
AND CONSTRAINT_NAME LIKE '%_foreign'")
->get();
// you need to write the next line to get back to your ..
// Original Database
DB::statement("USE <DATABASE_NAME>");
foreach ($data as $single) {
// Don't forget to add protected $single = null; in migration class
$this->single = $single;
Schema::table($single->TABLE_NAME, function (Blueprint $table) {
// drop the previous foreign key
$table->dropForeign($this->single->CONSTRAINT_NAME);
// New Foreign key
$table->foreign($this->single->COLUMN_NAME)->references($this->single->REFERENCED_COLUMN_NAME)
->onDelete("cascade")->on($this->single->REFERENCED_TABLE_NAME);
});
}
}
Final Note:
The purpose of this article was to show you how to make a major change to all your foreign keys.
feel free to contact me on my email ahmed.magdy.9611@gmail.com.
Thanks for coming to my Ted Talk.
Top comments (0)