Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Node.js, PHP and APIs

onDelete set null fails or is ignored in a Laravel migration

Errormigrate 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.

  • Applies to Laravel 12, MySQL 8.4, PHP 8.3
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 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:status
  2. 2Match what you see, then jump to its fix

    1. 1The migration fails when creating the constraint, or inserts of null failThe column is NOT NULLFix
    2. 2The column is still NOT NULL even though nullable() is in the chainnullable() comes after constrained()Fix
    3. 3Editing the old migration changes nothingThe table already exists with an old constraintFix
    4. 4The constraint cannot be created, with an old integer id as the parent keyThe column type does not match the parent keyFix

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*.php

2Fix

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:fresh

Confirmed by dev.mysql.com

Did this fix it?

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:fresh

Confirmed by laravel.com

Did this fix it?

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:status

2Fix

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 migrate

Confirmed by laravel.com

Did this fix it?

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:fresh

Confirmed by dev.mysql.com

Did this fix it?

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:

Related fixes

Back 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

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.