The easy way to change the primary column with int type into big integer type include all references column table
Last time I did some major upgrade for the Laravel framework on my several works from version 5.5 into version 6. Some of the application structure has been changed by the Laravel dev team. But it's not a big deal. I made several small updates on my source and everything works fine. Until I tried to run php artisan migrate:refresh, and whoops, I got an error when the migration running added a relation between table users and others.
After I checked the migration source and surfing on the internet, I found that the Laravel dev team change the default increment type from an integer type into a big integer type based on this link reference.
It will not be a big deal if we develop a new project using the last version of the Laravel Framework. But, it will make some problems when we upgrade the current project from the previous version into a new version of the Laravel framework with some of the libraries have a migration feature on it like my several libraries I made based on the Laravel framework at https://packagist.org/packages/webappid/
It will take time to look for one by one the reference column who use the column id in the users table and create one by one migration to change the current type into a new one. So, I decided to create a small migration script file to solve this problem.
First of all, just create a migration file in the Laravel project like usually. For example, create a migration file with a name ChangeIntToBigintColumn. And the migration code in the up function uses this one :
/**
* add the table name into array $tableList that you need to change the type from integer into biginteger
*/
$tableList = [
'users'
];
/**
* get all column and reference column from information schema need to alter
*/
$columnsToAlter = DB::table('information_schema.key_column_usage')
->select('table_name',
'column_name',
'constraint_name',
'referenced_table_name',
'referenced_column_name')
->where('constraint_schema', env('DB_DATABASE'))
->whereNotNull('referenced_table_name')
->whereIn('referenced_table_name', $tableList)
->get();
/**
* disable foreign key constraints optional only
*/
Schema::disableForeignKeyConstraints();
foreach ($columnsToAlter as $item) {
/**
* drop the foreign key
*/
Schema::table($item->table_name, function (Blueprint $table) use ($item) {
$table->dropForeign($item->constraint_name);
});
/**
* Drop the index column
*/
Schema::table($item->table_name, function (Blueprint $table) use ($item) {
$table->dropIndex($item->constraint_name);
});
/**
* Alter the column type
*/
Schema::table($item->table_name, function (Blueprint $table) use ($item) {
$table->bigIncrements($item->column_name)->change();
});
}
foreach ($columnsToAlter as $item) {
Schema::table($item->referenced_table_name, function (Blueprint $table) use ($item) {
/**
* change the referenced column name
*/
$table->unsignedBigInteger($item->referenced_column_name)->change();
});
}
foreach ($columnsToAlter as $item) {
Schema::table($item->table_name, function (Blueprint $table) use ($item) {
/**
* Restore the relation
*/
$table->foreign($item->column_name)->references($item->referenced_column_name)->on($item->referenced_table_name);;
});
}
You just need to add the table name you need to change the primary column type from an integer into a big integer into the array $tableList. Save the file, and run the php artisan migrate from the current project to change all column types.
Good Luck and Happy Coding