Clever Geek Handbook

Laravel - adding foreign key to existing table with data

I have an existing table objects

with data. Now I need to add a new table named holdings

and add a relation from objects to the table holdings

. 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 the correct database structure (like InnoDB), the fields exist and are of the correct type (int). The only difference is that the table is objects

filled with data and the table holdings

is new and empty.

+3


source to share


3 answers


Column

holding_id

it should be unsigned

create a new migration file and transfer it, the transition code should look something like this:

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

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


change()

method for changing the structure of a column

it doesn't have to be defined as well onDelete("NO ACTION")

+4


source


Thanks Mohammad, but this solution didn't work for me as I am Laravel 5.4

, and there is another case here where my other table already exists. Here is what I found, it might help someone.

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

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


with index()

and nullable()

he did the trick.

Edit No need index()

just neednullable()

+1


source


To add a foreign key, first make sure your column is marked unsigned.

Just add a line before your line:

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


source






More articles:


All Articles