How do I modify a column to null that has foreign key?

Question

I have to change the type of the user_id column from accounts to nullable, but it's a foreign key with the id column of users, how should it be done correctly?

I'm working with Laravel 5.6.27 and MySQL 5.6, I tried using the functions of the facade, but it did not work. Now I am testing with the statements and I have this error:

In Connection.php line 664: SQLSTATE[HY000]: General error: 1025 Error on rename of './gonano/#sql-c_b' to './gonano/accounts' (errno: 150 - For key constraint is incorrectly formed) (SQL: ALTER TABLE accounts CHANGE user_id user_id INT DEFAULT NULL)

create_users_table:

public function up(): void
{
    Schema::create('users', function (Blueprint $table): void {
        $table->increments('id');
        $table->string('first_name', 100);
        $table->string('last_name', 100);
        $table->string('email')->unique();
        $table->bigInteger('cuit')->unsigned();
        $table->string('password');
        $table->enum('status', ['activated', 'blocked'])->default('activated');
        $table->integer('country_id')->unsigned()->references('id')->on('countries');
        $table->timestampsTz();
    });
}

create_accounts_table:

public function up(): void
{
    Schema::create('accounts', function (Blueprint $table): void {
        $table->bigIncrements('id');
        $table->string('address')->default('');
        $table->integer('user_id')->index()->unsigned();
        $table->integer('system_id')->index()->unsigned()->default(0);
        $table->foreign('user_id')->references('id')->on('users');
        $table->timestampsTz();
    });
}

change_user_id_to_nullable_in_accounts:

public function up()
{
    Schema::disableForeignKeyConstraints();
    DB::table('accounts')->truncate();
    Schema::table('accounts', function (Blueprint $table): void {
        $table->integer('user_id')->nullable()->change();
    });
    Schema::enableForeignKeyConstraints();
}

I need that user_id can be nullable and I can not achieve it, thanks for the help

Possible duplicate of Migration: Cannot add foreign key constraint in laravel — pr1nc3
– pr1nc3, Commented Jan 24, 2019 at 13:20

Martijn Pieters · Accepted Answer · 2019-08-05 07:43:14Z

1

Create new migration for make nullable to your user_id.

and paste this code in your migration.

public function up()
{
    \Illuminate\Support\Facades\DB::statement('SET FOREIGN_KEY_CHECKS=0;');
    \Illuminate\Support\Facades\DB::table('accounts')->truncate();
    Schema::table('accounts', function (Blueprint $table) {
        $table->integer('user_id')->nullable()->change();
    });
    \Illuminate\Support\Facades\DB::statement('SET FOREIGN_KEY_CHECKS=1;');
}

edited Aug 5, 2019 at 7:43

Martijn Pieters

1.1m326 gold badges4.2k silver badges3.4k bronze badges

answered Jan 24, 2019 at 13:24

Prathamesh Doke

8552 gold badges19 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Indra Over a year ago

or use \Illuminate\Support\Facades\Schema::disableForeignKeyConstraints() as recommended in the manual and then \Illuminate\Support\Facades\Schema::enableForeignKeyConstraints()

Juan I. Castellano Over a year ago

Thanks for your answers Prathamesh and Indra, but I got a new error. I updated the question with the new error

Juan I. Castellano Over a year ago

@PrathameshDoke I also updated the code in the question

Collectives™ on Stack Overflow

How do I modify a column to null that has foreign key?

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related