onDelete set null fails or is ignored in a Laravel migration
migrate fails when a foreign key uses onDelete('set null')Short answer
SET NULL only works when the foreign key column allows NULL. Declare the column nullable before calling constrained, then add nullOnDelete. On an existing table, drop the old foreign key and make the column nullable before adding it again. Check that the column type matches the parent key.
Fix it fast
1Find out which cause you have
Check which migrations actually ran, then read the column and its constraint in the migration file, in order. The order of the chained calls matters.
php artisan migrate:status2Match what you see, then jump to its fix
- 1
The migration fails when creating the constraint, or inserts of null failThe column is NOT NULLFix - 2
The column is still NOT NULL even though nullable() is in the chainnullable() comes after constrained()Fix - 3
Editing the old migration changes nothingThe table already exists with an old constraintFix - 4
The constraint cannot be created, with an old integer id as the parent keyThe column type does not match the parent keyFix
- 1
What does the error look like?
migrate fails when a foreign key uses onDelete('set null')Deleting the parent row does not set the child column to NULL
Cause 1 of 4
The column is NOT NULL
1Check
MySQL says to make sure the child columns are not declared NOT NULL when you specify SET NULL. A plain foreignId column is NOT NULL.
grep -n "foreignId" database/migrations/*posts*.php2Fix
Make the column nullable and use nullOnDelete.
$table->foreignId('user_id')
->nullable()
->constrained()
->nullOnDelete();3Verify
Run the migration on a fresh database, delete a parent row, and check the child's column.
php artisan migrate:freshConfirmed by dev.mysql.com
Cause 2 of 4
nullable() comes after constrained()
1Check
Laravel requires any extra column modifier to be called before constrained. A modifier chained after it is too late.
grep -rn "constrained()" -A 2 database/migrations/2Fix
Reorder the chain: column, modifiers, constrained, then the delete action.
// wrong
$table->foreignId('user_id')->constrained()->nullable()->nullOnDelete();
// right
$table->foreignId('user_id')->nullable()->constrained()->nullOnDelete();3Verify
Read the foreign key list again after migrating.
php artisan migrate:freshConfirmed by laravel.com
Cause 3 of 4
The table already exists with an old constraint
1Check
A migration that already ran is not run again. The old constraint, with its old delete rule, stays in the database.
php artisan migrate:status2Fix
Write a new migration. Drop the foreign key by its name or by an array with the column name, make the column nullable, then add the constraint again with nullOnDelete.
Schema::table('posts', function (Blueprint $table) {
$table->dropForeign(['user_id']);
$table->unsignedBigInteger('user_id')->nullable()->change();
$table->foreign('user_id')->references('id')->on('users')->nullOnDelete();
});3Verify
Run the new migration, delete a parent row and look at the child column.
php artisan migrateConfirmed by laravel.com
Cause 4 of 4
The column type does not match the parent key
1Check
MySQL needs similar data types, and the size and sign of integer types must be the same. foreignId creates an UNSIGNED BIGINT, while increments() creates an UNSIGNED INTEGER primary key, so a foreignId pointing at an increments() parent does not match.
grep -rn "increments(\|bigIncrements(\|->id()" database/migrations/2Fix
Create the child column with the same type as the parent key, for example unsignedInteger when the parent uses increments().
$table->unsignedInteger('user_id')->nullable();
$table->foreign('user_id')->references('id')->on('users')->nullOnDelete();3Verify
The migration runs without a constraint error.
php artisan migrate:freshConfirmed by dev.mysql.com
How do I stop it coming back?
- Decide the delete rule when you design the relation: cascade, restrict or null.
- Never edit a migration that already ran on a shared database; add a new one.
- Keep key types consistent across tables.
Sources
How was this page made?
I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.
Which stack is behind this page?
Projects of mine built on the same technologies:
- Spotless LaundryAn on-demand laundry app: Flutter on a Laravel back end, with Firebase for live updates.
- EZ EstimaterConstruction quoting and job tracking for contractors, on a Laravel and Firebase stack.
- TaxiDexMy own multi-tenant taxi dispatch SaaS: API, dispatcher portal, driver and passenger apps, and the servers underneath.
Related fixes
Back end
Laravel insert fails with 'Field user_id doesn't have a default value'
The insert works when the column is removed or made nullable
Error 1364 means Laravel sent an INSERT without a value for a NOT NULL column that has no default, and MySQL strict mode refuses it.
3 causes, each with a fixBack end
Laravel migration cannot drop a column that has a foreign key
Error on rename of '%s' to '%s' (errno: %d - %s)
MySQL refuses to drop a column while a foreign key constraint still uses it.
2 causes, each with a fixBack end
Laravel: distinct() and count() give the total instead of the unique count
select('city')->distinct()->count() ignores the select
Laravel builds the count from the column you pass to count(), not from select().
2 causes, each with a fixBack end
Laravel Eloquent query for a column that is X or NULL
where('column', null) returns no rows
Wrap the pair in a closure: where(function ($q) { $q->where('col', $x)->orWhereNull('col'); }).
3 causes, each with a fixBack end
Laravel insert fails with Invalid datetime format 1292 Incorrect datetime value
SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value
MySQL ran in strict mode and refused a date or time value that it cannot store.
3 causes, each with a fixBack end
php artisan migrate stops with could not find driver
could not find driver (Connection: mysql, SQL: select * from information_schema.tables ...)
Laravel talks to the database through a PDO driver, and this error means the PHP that runs artisan has none for your DB_CONNECTION.
3 causes, each with a fix