Laravel migrations - foreign key not applying to the table

Question

I have tried add foreign key constraint using migrations. The migration completes without any error. However, when the database is checked, the foreign key constraint is not added to the table. Other things specified in the migrations work fine except of FK constraint.

My pivot table with FKs:

Schema::create('book_author', function (Blueprint $table) {

            $table->integer('book_id')->unsigned();
            $table->integer('author_id')->unsigned();

            $table->foreign('book_id')->references('id')->on('book')->onDelete('restrict')->onUpdate('cascade');
            $table->foreign('author_id')->references('id')->on('author')->onDelete('restrict')->onUpdate('cascade');
            $table->primary(['book_id','author_id']);

        });
        Schema::enableForeignKeyConstraints();

Author table:

Schema::create('author', function (Blueprint $table) {
            $table->increments('id');
            $table->string('email', 250)->unique();
        });

Book table:

 Schema::create('book', function (Blueprint $table) {
            $table->increments('id');
            $table->string('title');
        });

Is there anything I'm missing here?

Which database? How are you checking to make sure the foreign key is there? — user1669496
– user1669496, Commented Jan 20, 2017 at 17:21
Have you tried making the changes I suggested in my answer? If so is the problem still there? — Mihailo
– Mihailo, Commented Jan 20, 2017 at 18:04
Yes. Same result. No difference. BTW I didn't down vote your answer. — paarandika
– paarandika, Commented Jan 20, 2017 at 18:24

Felippe Duarte · Accepted Answer · 2017-01-20 18:42:50Z

1

MyISAM doesn't support foreign key: http://dev.mysql.com/doc/refman/5.7/en/myisam-storage-engine.html

Run the following command to make sure which engine your table has:

SHOW TABLE STATUS WHERE Name = 'book_author'

If it shows MyISAM, you have two option:

don't use FK at all
Replace your engine

I would go with option 2, because you are creating a new table and there is no risk to lose data here.

You can force in your migrations with:

$table->engine = 'InnoDB';

Alternatively, you can run this command manually:

ALTER TABLE book_author ENGINE=INNODB

answered Jan 20, 2017 at 18:42

Felippe Duarte

15.2k2 gold badges27 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mihailo · Accepted Answer · 2017-01-20 17:24:01Z

0

Change your book_author migration to:

Schema::create('book_author', function (Blueprint $table) {
    $table->integer('book_id')->unsigned()->index();
    $table->integer('author_id')->unsigned()->index();

    $table->foreign('book_id')->references('id')->on('book')->onDelete('cascade')->onUpdate('cascade');
    $table->foreign('author_id')->references('id')->on('author')->onDelete('cascade')->onUpdate('cascade');
});

This should work.

On the other note you're breaking a few Laravel conventions with your table names:

Name your tables in plural form (authors & books)
The pivot table (book_author) should be called (author_book) - alphabetical priority.

answered Jan 20, 2017 at 17:24

Mihailo

4,9974 gold badges24 silver badges31 bronze badges

Collectives™ on Stack Overflow

Laravel migrations - foreign key not applying to the table

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related