[SOLVED] Laravel - add foreign key on existing table with data

Laravel - add foreign key on existing table with data

I have existing table objects with data. Now I need to add new table named holdings and add a relation from objects to holdings table. In the migration file, I print this:

$table->foreign('holding_id')->references('id')->on('holdings')->onDelete("NO ACTION");

and get this error when trying to migrate

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update 
a child row: a foreign key constraint fails (`kolomnaoffice`.`#sql-f10_126` 
CONSTRAINT `objects_holding_id_foreign` FOREIGN KEY (`holding_id`) 
REFERENCES `holdings` (`id`) ON DELETE NO ACTION) (SQL: alter table `objects` add constraint 
`objects_holding_id_foreign` foreign key (`holding_id`) references `holdings` 
(`id`) on delete NO ACTION)

I have correct database structure (both InnoDB), the fields exist and have correct type (int). The only thing different is that the table objects is filled with data, and table holdings is new and empty.

Solution

The holding_id column should be unsigned

Create a new migration file and migrate it, migration code should be like this :

Schema::table('objects', function (Blueprint $table) {
    $table->integer('holding_id')->unsigned()->change();

    $table->foreign('holding_id')->references('id')->on('holdings');
});

The change() method is called to change the structure of existing column.

It's not necessary to call onDelete("NO ACTION") method.