CODEWITHSUNDEEP
phplaravel
sa-fm
sa-fm

Reputation: 456

set existing foreign key column to nullable in alter table migration

I first created a migration like this:

Schema::create('table1',function(Blueprint $table){
        $table->bigIncrements('id');
        $table->string('name')->unique();
        $table->integer("user_id")->unsigned();
        $table->foreign("user_id)->references("id")->on("users");
});

Then i wanted to add nullable property to user_id column , i wrote this migration:

Schema::table('f_subjects', function (Blueprint $table) {
        $table->integer('user_id')->nullable()->change();
        $table->foreign('original_law_id')->references('id')->on('f_original_law');
    });

But i got this error:

Cannot change column 'user_id': used in a foreign key constraint 'table1_user_id_foreign'

Upvotes: 9

Views: 8174

Answers (4)

asghar
asghar

Reputation: 447

Run php artisan make:migration change_user_id_to_nullable_in_table1_table --table=table1 in your project root folder.

In the new migration file write this code.

Schema::table('table1', function (Blueprint $table) {
  $table->unsignedBigInteger('user_id')->nullable()->change();
});

Then run php artisan migrate.

if that does not work, check your code. Maybe you set your foreign key field to zero or other wrongs value instead of the parent table ID.

Upvotes: 1

ramin ashrafimanesh
ramin ashrafimanesh

Reputation: 1062

1- Delete your foreign key

$table->dropForeign('table1_user_id_foreign');

2- Change user_id column definition:

//If user_id is not unsigned remove unsigned function
$table->integer('user_id')->nullable()->unsigned()->change();

3- Create index

$table->foreign('user_id')->references('id')->on('users');

Complete migration:

Schema::table('table1',function(Blueprint $table){
    //Or disable foreign check with: 
    //Schema::disableForeignKeyConstraints();
    $table->dropForeign('table1_user_id_foreign');
    $table->integer('user_id')->nullable()->unsigned()->change();
    //Remove the following line if disable foreign key
    $table->foreign('user_id')->references('id')->on('users');
});

Upvotes: 11

parastoo
parastoo

Reputation: 2469

Always use these codes in migrations: 

 public function down()
    {

        Schema::disableForeignKeyConstraints();
        Schema::dropIfExists('table');
        Schema::enableForeignKeyConstraints();
    }

Upvotes: 2

Alexey Mezenin
Alexey Mezenin

Reputation: 163768

1. You need to drop the constraint first:

$table->dropForeign(['user_id']);

2. Or you could temporarily disable FK constraints:

Schema::disableForeignKeyConstraints();

And then enable constraints:

Schema::enableForeignKeyConstraints();

https://laravel.com/docs/5.5/migrations#foreign-key-constraints

Upvotes: 7

Related Questions