Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails

I'm trying to update a table with a foreign key of a new model i created. migrations schema:

\migrations
....
2023_11_21_100552_create_services_table.php
....
....
....
2023_12_14_141745_create_quotes_table.php
....
....
....
2023_12_21_105900_add_service_id_to_quotes_table.php

and 2023_12_21_105900_add_service_id_to_quotes_table.php

    public function up(): void
    {
        Schema::table('quotes', function (Blueprint $table) {
            $table->foreignId('service_id')
            ->constrained();
        });
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        Schema::table('quotes', function (Blueprint $table) {
            $table->dropForeign('service_id');
        });
    }

but when running the migrations it throws this error:

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`shareit`.`#sql-1_795`, CONSTRAINT `quotes_service_id_foreign` FOREIGN KEY (`service_id`) REFERENCES `services` (`id`) ON DELETE CASCADE ON UPDATE CASCADE) (Connection: mysql, SQL: alter table `quotes` add constraint `quotes_service_id_foreign` foreign key (`service_id`) references `services` (`id`) on delete cascade on update cascade)

if I try to make the foreign key this way:

            $table->unsignedBigInteger('service_id');

            $table->foreign('service_id')->references('id')->on('services');

php artisan migration throws

   Doctrine\DBAL\Schema\Exception\ColumnDoesNotExist 

  There is no column with name "service_id" on table "quotes".

It can't see whats wrong.

Tray2

2 years ago

Level 73

I'm guessing here that you have data in that table, and one of those records references a foreign key that does not exist.

AlessandroCursoli

2 years ago

Level 1

@Tray2 yes, i forgot to mention that i have data inside quotes table. will it change if i create a dummy record for services?

AlessandroCursoli

2 years ago

Level 1

dummy record created but nothing changed

Tray2

2 years ago

Best Answer

Level 73

@AlessandroCursoli That depends, I'm guessing that the service_id in your quotes table is either null or filled with dummy data, each entry in the quotes table needs to have a service_id that exists in the services table. There is a way around this, but it will give you inconsistent data in your database.

you can try to disable it for the schema and then add the constraint, and reactivate the constraints.

Schema::disableForeignKeyConstraints();

Schema::table('quotes', function (Blueprint $table) {
            $table->foreignId('service_id')
            ->constrained();
        });

Schema::enableForeignKeyConstraints();

AlessandroCursoli

2 years ago

Level 1

@Tray2 Thank you it works now

Please or to participate in this conversation.