How to set a foreign key to nullable in Laravel

4
votes

I have a user_id set in log table and now have a problem that I sometimes need to store logs to actions performed by non-logged users.

When I run this migration 

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

I get an error

[Illuminate\Database\QueryException] SQLSTATE[HY000]: General error: 1832 Cannot change column 'user_id': used in a foreign key constraint 'users_log_user_id_foreign' (SQL: ALTER TABLE users_log CHANGE user_id user_id INT UNSIGNED DEFAULT NULL)

1
phpmysqllaravel
you have a foreign key constraint that does not allow null value in user_id. Remove the constraint and you should be fine. – Unex

1 Answers

12
votes

You need to drop FK constraint first:

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

Then modify the column and add a new FK constraint.

Also, make sure you're doing this in a separate Schema::table() closures.

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