Monday 10 February 2020

Laravel Eloquent drop/rename column, rerun migration, MSSQL default value issue

MSSQL default value of a column issue:

If a column in MSSQL server has a default value, it has a default constraint.
The constraint must be dropped , before the column can be dropped

for example :

alter table CompanyTransactions drop constraint [df__CompanyTr__Creat__0cdae408];

alter table CompanyTransactions drop column [Created];

How to use Laravel to drop a column in MSSQL with default value ?
1. php artisan make:migration drop_column_to_my_table
2.    public function up()
    {
        Schema::table('my_table', function (Blueprint $table) {
            //
            $table->dropColumn('my_cloumn');
        });
    }
run the migration
php artisan migrate --path=/database/migrations/<file_name>
Exception will thrown indicating the column has a constraint(default value constraint in MSSQL)
goto MSSQL manager , drop constraint <contraint_name>
run migration again

https://stackoverflow.com/questions/43549413/alter-table-drop-column-failed-because-one-or-more-objects-access-this-column

La-ravel Eloquent drop column:
1. php artisan make:migration drop_column_to_my_table
2.    public function up()
    {
        Schema::table('my_table', function (Blueprint $table) {
            //
            $table->dropColumn('my_cloumn');
        });
    }


La-ravel Eloquent rename column:
1. php artisan make:migration rename_column_to_my_table
2.       public function up()
    {
        Schema::table('my_table', function (Blueprint $table) {
            //
            $table->renameColumn('my_current_column_name', 'new_name');
        });
    }


La-ravel Eloquent change column:
1. php artisan make:migration change_column_to_my_table
2.       public function up()
    {
        Schema::table('my_table', function (Blueprint $table) {
            // MediumText is new  type
             $table->mediumText('new_column')->nullable()->change();
        });
    }

La-ravel Eloquent change column:
1. php artisan make:migration add_column_to_my_table
2.       public function up()
    {
        Schema::table('my_table', function (Blueprint $table) {
            // integer is new column type
             $table->integer('new_column')->default(0);
        });
    }

La-ravel Eloquent re run already executed migration:
php artisan migrate --path=/database/migrations/migration_file_name.php
if the application indicates migration already ran.
Go to your DB(ex SQL server), find migrations table, delete the already executed migration record
Then re run php artisan migrate --path=/database/migrations/migration_file_name.php
If encountered unable to drop column with default value issue, run the SQL command drop the default value constraint and then re run the drop column migration

Further read on migration need to drop default constraint :
https://github.com/laravel/framework/issues/4402

No comments:

Post a Comment