CODEWITHSUNDEEP
mysqldatabaselaravelforeign-keys
Ricardo Jesus Jarquin Perez
Ricardo Jesus Jarquin Perez

Reputation: 2569

SQL violation adding foreign key, Laravel

I need your help! :( I'm trying to add foreign key in a existing table with data, but i got a error i think is because the table has data, I tested all migrations in a new DB and all worked fine, someone know if is possible add the foreign key a table with data??

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

        $table->integer('shiftId')->unsigned()->change();
        $table->foreign('shiftId')->references('id')->on('shifts');

    });

This when just run the migration to add the foreign key This when just run the migration to add the foreign key

This is when run all the migrations

enter image description here

Upvotes: 0

Views: 118

Answers (1)

Marlin Pierce
Marlin Pierce

Reputation: 10089

You cannot add a foreign key constraint if you have existing data which violates the new constraint. To find the data which violates the constraint, do an outer join.

SELECT circles.*
FROM circles
     LEFT OUTER JOIN shifts
       ON circles.shiftId = shifts.id
WHERE shifts.id IS NULL

Upvotes: 2

Related Questions