Add documentation on foreign keys for creating key when there is existing data, will throw error unless nullable

[drewgallagher]

I just tried creating a foreign key for a table called tasks in a migration.

Tasks should have a foreign key strategy_frequency_id that points to id on tasks

Because tasks is an existing table with many records, when tried to create the foreign_key it threw the following error

   Illuminate\Database\QueryException 

  SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`nova`.`#sql-1_a73a`, CONSTRAINT `tasks_strategy_frequency_id_foreign` FOREIGN KEY (`strategy_frequency_id`) REFERENCES `strategy_frequencies` (`id`)) (SQL: alter table `tasks` add constraint `tasks_strategy_frequency_id_foreign` foreign key (`strategy_frequency_id`) references `strategy_frequencies` (`id`))

  at vendor/laravel/framework/src/Illuminate/Database/Connection.php:712
    708▕         // If an exception occurs when attempting to run a query, we'll format the error
    709▕         // message to include the bindings with SQL, which will make this exception a
    710▕         // lot more helpful to the developer instead of just the database's errors.
    711▕         catch (Exception $e) {
  ➜ 712▕             throw new QueryException(
    713▕                 $query, $this->prepareBindings($bindings), $e
    714▕             );
    715▕         }
    716▕     }

      +9 vendor frames 
  10  database/migrations/2023_09_15_135715_add_strategy_frequency_to_task_table.php:19
      Illuminate\Support\Facades\Facade::__callStatic("table")

      +22 vendor frames 
  33  artisan:35
      Illuminate\Foundation\Console\Kernel::handle(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))

The cause was not clear, and I remembered after about 30 min of debugging that if there is data, and you try to apply a foreign key with existing records that don't have a tie to that foreign key, it will fail.

One workaround is to add ->nullable() on the new foreign key column so that existing records don't cause an issue.

This is done like so:

            Schema::table(self::TABLE_NAME, function (Blueprint $table) {
                $table->unsignedBigInteger(self::COLUMN_NAME)->nullable();
                $table->foreign(self::COLUMN_NAME)->references('id')->on('strategy_frequencies');
            });

Once I did that, the migration passed.

Can we add documentation in laravel for this scenario here (for all versions of laravel supported)? https://laravel.com/docs/10.x/migrations#foreign-key-constraints

Otherwise it may be hard to figure this out again if I forget the solution and I'm sure other devs run into this issue periodically

[shaedrich]

Maybe, people don't want want the column to be nullable in general. Alternatively, you may want to do this:

Schema::withoutForeignKeyConstraints(function () {
    $table->unsignedBigInteger(self::COLUMN_NAME);
    table->foreign(self::COLUMN_NAME)->references('id')->on('strategy_frequencies');
});

[drewgallagher]

Will this remove the foreign key constraint? Because the goal is to have it still

[shaedrich]

Why would it remove the constraint? It just doesn't throw your error message.

[drewgallagher]

I thought it might just because of the name of the function withoutForeignKeyConstraints implying that there isn't a key constraint.

However, I think the docs would still be helpful here.

I don't see any advice to use Schema::withoutForeignKeyConstraints on the docs in that section either

[shaedrich]

Oh, right—I didn't catch that. Sorry!

All it does, is doing SET FOREIGN_KEY_CHECKS=0; before and SET FOREIGN_KEY_CHECKS=1; after.