Laravel - add foreign key on existing table with data

Question

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.

Try making it the column nullable – ka_lin Jun 25 '17 at 08:52 — ka_lin, Jun 25 '17 at 08:52

score 22 · Accepted Answer · edited Feb 10 '19 at 20:56

22

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.

edited Feb 10 '19 at 20:56

Saleh Mahmood

1,823
1
22
30

answered Jun 25 '17 at 19:10

Mohammad Fanni

4,095
3
28
52

5

how about the down() function?. in case i need to rollback the migration? – Franz Dec 02 '20 at 00:55
change is for when there is a column before but for exist table and data we need to add foreign key with null inex like ``[..] ->index()->nullable();`` – nasrin.mkh Feb 01 '21 at 09:04

Yousef Altaf · Answer 2 · 2018-04-11T12:31:21.343

10

Thanks Mohammad but this solution didn't work for me as I am Laravel 5.4 and have different case here that my other table is already exists, Here what I found may it help some one.

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() it made the trick.

Edit No need for index() it just need to be nullable()

edited Apr 11 '18 at 12:31

answered Apr 11 '18 at 12:07

Yousef Altaf

2,631
4
46
71

2

I wonder if a nullable foregin key is a good idea? correct me if i'm worong – toing_toing May 30 '18 at 10:07
2

@toing_toing in my case the table was full of data witch will make it very difficult to add a foreign key without a default value so `nullable()` can be a good solution, then if you like to change that latter when you change you data. – Yousef Altaf May 30 '18 at 16:37

score 0 · Answer 3 · edited Jun 27 '17 at 16:14

0

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

Just add a line before your line:

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

edited Jun 27 '17 at 16:14

sisve

19,501
3
53
95

answered Jun 25 '17 at 15:26

ajit

56
6

score 0 · Answer 4 · answered Jul 23 '22 at 12:44

If you have a large table, say 1Million+ records, they I guess the best plan of operation is to:

Create a field with unsigned int datatype
Make sure new insertions to the table will follow to add value to the column. So change your insert queries accordingly
Create a queue to update older records and run it
After the the queue finishes, convert the field into foreign key

Laravel - add foreign key on existing table with data

4 Answers4

Linked