Framework: Cannot create nullable constrained foreign key

[hailwood]

• Laravel Version: 7.6.2
• PHP Version: 7.4.4
• Database Driver & Version: Mysql 5.7.29

Description:

Attempting to create a foreign ID that is both nullable and constrained seems to ignore the nullable aspect

Steps To Reproduce:

Attempt to run the following against a database table with existing rows in the table

$table->foreignId('user_id')->constrained()->nullable();

Instead of working you'll get hit with a QueryException

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`laravel`.`#sql-14_2ae0`, CONSTRAINT `visits_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)) (SQL: alter table `visits` add constraint `visits_user_id_foreign` foreign key (`user_id`) references `users` (`id`))

[driesvints]

Was the column already nullable? Don't you need to add the ->change() method after this? Can you post your full migrations?

[hailwood]

Hey @driesvints

Noit's when adding a new column to a table.

E,g,

Schema::create('users', function(Blueprint $table) {
    $table->id();
    $table->string('name');
}):

Schema::create('posts', function(Blueprint $table) {
    $table->id();
    $table->string('name');
}):

factory(Post::class, 10)->create();

Schema::table('posts', function(Blueprint $table) {
    $table->foreignId('user_id')->constrained()->nullable();
}):

[driesvints]

These are separate migrations?

[hailwood]

Correct, First two would be run as you're setting up the app,
Use it for a while so you've got some posts,
then at a later date you want to start associating new posts with a user.

[driesvints]

If you switch the constrained and nullable method it works for me.

$table->foreignId('user_id')->nullable()->constrained();

[salindalakmal]

https://laravel.com/docs/7.x/database#configuration

DB_FOREIGN_KEYS=true

[hailwood]

@salindalakmal not sure how that's related? We're not using sqlite.

[akr4m]

$table->foreignId('user_id')->constrained()->nullable();

$table->foreignId('user_id')->nullable()->constrained();

Put nullable() before the constrained()