In this post I will show you how to take advantage of Doctrine, which is a library that is included in laravel as well as in other frameworks, and it allows you to manage your php migration files. I recently had to update a database with a certain number of tables and the truth is that just by looking at it I got tired of thinking about having to do a lot of sequential migrations that practically followed the same logic.
I am going to leave you the commented code in case you want to place this migration file in your own laravel project.
Well the first step is obviously to create your migration using this command
php artisan make:migration rebuild_foreign_keys_with_on_delete_cascade_on_update_cascade
Once our migration file is created, we enter it and create the functions that I will explain within the code:
<?php
use IlluminateSupportFacadesSchema;
use IlluminateDatabaseSchemaBlueprint;
use IlluminateDatabaseMigrationsMigration;
class RebuildForeignKeysWithOnDeleteCascadeOnUpdateCascade extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
// We connect to the DB and we call DoctrineSchemaManager to perform the magic for us.
$con = Schema::getConnection()->getDoctrineSchemaManager();
// Here we retrieve all the database table names
$tb_names = $con->listTableNames();
// Now we start to retrieve all the foreign key for each table
foreach ($tb_names as $tb_name){
// Here the table name will be used in the function that laravel docs indicate to modify columns and other table attributes.
Schema::table($tb_name, function (Blueprint $table) use ($tb_name, $con){
// Here we retrieve all foreign keys from this specific table
$foreignKeys = $con->listTableForeignKeys($tb_name);
// First of all we obviously check if its empty in order to evaluate if it should be executed or not.
if (count($foreignKeys))
{
/* Once inside, we make sure that there are not foreign keys and we proceed to the update from ON DELETE NO ACTION to ON DELETE CASCADE
We loop key by key */
foreach ($foreignKeys as $foreignKey)
{
// Here we retrieve the local column, that means the column that saved the id of the foreign key relationship.
$local_col = $foreignKey->getLocalColumns()[0];
// Here we retrieve the name of our foreign table
$foreign_table = $foreignKey->getForeignTableName();
// Here we retrieve our foreign key column
$foreign_col = $foreignKey->getForeignColumns()[0];
// Here we retrieve the foreign key name
$fk_name = $foreignKey->getName();
/* Now we proceed to break the foreign relationship, which is the proper way to rebuild a foreign key relationship.*/
$table->dropForeign($fk_name);
/* Once the foreign key relationship has been broken , we rebuild it taking into account that new events should be ON DELETE and ON UPDATE CASCADE */
$table
->foreign($local_col, $fk_name)
->references($foreign_col)
->on($foreign_table)
->onUpdate('CASCADE')
->onDelete('CASCADE');
}
}
});
}
}
/**
* Reverse the migrations.
*
* @return void
*/
/* The rollback migration function should be almost the same with only one differente which is to switch back the foreign key relationship to ON DELETE NO ACTION AND ON UPDATE NO ACTION.*/
public function down()
{
$con = Schema::getConnection()->getDoctrineSchemaManager();
$tb_names = $con->listTableNames();
foreach ($tb_names as $tb_name)
{
Schema::table($tb_name, function (Blueprint $table) use ($tb_name, $con)
{
$foreignKeys = $con->listTableForeignKeys($tb_name);
if (count($foreignKeys))
{
foreach ($foreignKeys as $foreignKey)
{
$local_col = $foreignKey->getLocalColumns()[0];
$foreign_table = $foreignKey->getForeignTableName();
$foreign_col = $foreignKey->getForeignColumns()[0];
$fk_name = $foreignKey->getName();
$table->dropForeign($fk_name);
$table
->foreign($local_col, $fk_name)
->references($foreign_col)
->on($foreign_table)
->onUpdate('NO ACTION')
->onDelete('NO ACTION');
}
}
});
}
}
}
I hope that this method has helped you. If you have any suggestions, questions or improvements you can write to me and I will try to answer them as soon as possible.
Top comments (0)