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, Jan 20 '17 at 17:21
Have you tried making the changes I suggested in my answer? If so is the problem still there? — Mihailo, Jan 20 '17 at 18:04
Yes. Same result. No difference. BTW I didn't down vote your answer. — paarandika, Jan 20 '17 at 18:24
@FelippeDuarte I didn't change the default engine. Therefore, I think its MyISAM. — paarandika, Jan 20 '17 at 18:27
MyISAM doesn't support foreign keys. Please, run `SHOW TABLE STATUS WHERE Name = 'book_author'` and check which engine your table has. — Felippe Duarte, Jan 20 '17 at 18:38
Ah... Such a shame... Np thanks for the info, it's always strange when people downvote without commenting. I'll leave my answer here in case somebody else finds it useful. — Mihailo, Jan 20 '17 at 19:50

score 1 · Accepted Answer · answered Jan 20 '17 at 18:42

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

score 0 · Answer 2 · answered Jan 20 '17 at 17:24

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.

Laravel migrations - foreign key not applying to the table

2 Answers2