I want to add a foreign key in a table that already has data and is created. I get this error when I try to migrate the new migration file.
Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails.
//add_driving_id_as_foreign_key_to_persons_table.php
return new class extends Migration
{
/**
* Run the migrations.
*/
public function up(): void
{
Schema::table('persons', function (Blueprint $table) {
$table->unsignedBigInteger('driving_car_id')->nullable();
$table->foreign('driving_car_id')->references('id')->on('cars');
});
}
/**
* Reverse the migrations.
*/
public function down(): void
{
Schema::table('persons', function (Blueprint $table) {
$table->dropForeign(['driving_car_id']);
$table->dropColumn(['driving_car_id']);
});
}
};
Do I need to make it nullable or is there any safer way?
Yes, I think you should use nullable in foreign key definition too, documentation says it should be defined before other modificators:
$table->foreign('driving_car_id')->nullable()->references('id')->on('cars');
Also check that cars.id column has unsigned big integer type too, I suspect foreign key and referenced column types must match strictly.
But that would mean it would allow null values though. Is there a way to add the key without making it nullable?
Disabling constraints before adding the key then re-enabling it again could do it. 🤔🤔🤔
Oh wait, that will cause 1364 error. Maybe nullable is necessary 🤔🤔🤔
If you don't want the column to be nullable you should provide a default value, moreover the value must exist in cars.id column. When new column is created in existing table it should be filled with values, right?
Let's say a row with id 123 exists in cars table:
$table->unsignedBigInteger('driving_car_id')->default(123);
$table->foreign('driving_car_id')->references('id')->on('cars');
As for me, nullable driving_car_id column makes more sense. A person is allowed to not be driving any car (in other words, to have driving_car_id = NULL), isn't he?
I would advise CAUTION with migrations on a live database if that is what you are planning. Backup first. You could loose data if not careful.
Yes, I already did a backup. Fortunately, I did not forget it.
@chron You first need to work out why the constraint is failing. It will fail for one of two reasons:
10 in but the other table does not have a row with a primary key of 10, then the constraint is going to fail (because your foreign key would be pointing to a non-existent record).So, you need to sort the above out first before you can create the foreign key, because at least one of the constraints above is not satisfied.
Sounds to me that you want to add a driver to the car in a one to many relation, I wouldn't do it that way, since in my mind a driver can drive multiple cars, and a car can be driven by multiple drivers. So I 'd add a pivot to handle that relation, that way you don't need to risk the existing data.
In previous thread OP mentioned "currently driving", which I understood as at the moment a single car is being driven by a single driver, not multiple. And vice versa, a person can be driving only one car in one point in time.
Agreed, and in that case the field can be a nullable foreign key, however I would still use a pivot for it, since the driver is related to the car, and not a part of the car. :)
Please or to participate in this conversation.