CODEWITHSUNDEEP
mysqllaravel-5
Christian Giupponi
Christian Giupponi

Reputation: 7618

Laravel - Can't solve Integrity constraint violation

I have created a new migration to add a column to an existing table and add a foreign key to an existing table.

This is the migration with the new column:

Schema::table( 'events', function ( Blueprint $table ) {

     $table->integer( 'category_id' )->unsigned()->after( 'place_id' );
     $table->foreign('category_id')->references('id')->on('categories');
 } );

When I run the migrate command I get:

[Illuminate\Database\QueryException]                                                                                                                                           
  SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`meetmount`.`#sql-3c8_424`, CONSTRAINT `events_catego  
  ry_id_foreign` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`)) (SQL: alter table `events` add constraint events_category_id_foreign foreign key (`category_id`) r  
  eferences `categories` (`id`))

and

[PDOException]                                                                                                                                                                 
  SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`meetmount`.`#sql-3c8_424`, CONSTRAINT `events_catego  
  ry_id_foreign` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`))

How can I solve?

Upvotes: 1

Views: 681

Answers (2)

Harold Andres Muñoz
Harold Andres Muñoz

Reputation: 1

Happened the same with me, but I resolved this adding a default value on creation of column:

$table->integer( 'category_id' )->unsigned()->after( 'place_id' )->default(1);

Make sure the table "categories" has a record with id = 1.

Upvotes: 0

Ahmad Baktash Hayeri
Ahmad Baktash Hayeri

Reputation: 5880

The cause of the error could be the following:

The parent table categories and/or the child table events already have records in them. As you want to reference the parent table's particular column (id), MySQL will expect the child table to have a value that exists in the parent, hence the integrity constraint violation.

A possible solution (in case the parent is not empty) is to add a default value in the events table for the foreign key column:

$table->integer( 'category_id' )->unsigned()->after( 'place_id' );
$table->foreign('category_id')->references('id')->on('categories')->default(1);

Upvotes: 1

Related Questions