CODEWITHSUNDEEP
phpmysqllaravelmigration
Proudje
Proudje

Reputation: 29

Laravel Migrations - General error: 1215 Cannot add foreign key constraint

I have multiple tables with multiple foreign keys, but when I migrate it can not find the other migration yet because it is not made yet.

SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint (SQL: alter table `post` add constraint `post_vote_id_foreign` foreign key (`vote_id`) references `vote` (`id`))

Post table

    Schema::create('post', function (Blueprint $table) {
        $table->id();
        $table->unsignedBigInteger('topic_id');
        $table->unsignedBigInteger('user_id');
        $table->unsignedBigInteger('vote_id');
        $table->string('title');
        $table->string('summary');
        $table->string('image');
        $table->timestamps();

        $table->foreign('topic_id')->references('id')->on('topic');
        $table->foreign('user_id')->references('id')->on('users');
        $table->foreign('vote_id')->references('id')->on('vote');

    });

Vote table to see all the votes for the post

        Schema::create('vote', function (Blueprint $table) {
            $table->id();
            $table->unsignedBigInteger('post_id');
            $table->unsignedBigInteger('user_id');
            $table->timestamps();

            $table->foreign('post_id')->references('id')->on('post');
            $table->foreign('user_id')->references('id')->on('users');

        });

Upvotes: 0

Views: 1524

Answers (3)

Samuel Aiala Ferreira
Samuel Aiala Ferreira

Reputation: 694

For googlers ... in my case it was because the parent table was myIsam type and not innodb

Upvotes: 0

Donkarnash
Donkarnash

Reputation: 12835

Although it's not good to have circular references - most probably indicates bad design. However if you really want to have the circular reference

Schema::create('post', function (Blueprint $table) {
    $table->id();
    $table->unsignedBigInteger('topic_id');
    $table->unsignedBigInteger('user_id');
    $table->unsignedBigInteger('vote_id');
    $table->string('title');
    $table->string('summary');
    $table->string('image');
    $table->timestamps();

    $table->foreign('topic_id')->references('id')->on('topic');
    $table->foreign('user_id')->references('id')->on('users');
});

You can alter the post table to define the foreign key after the vote table has been created

Schema::create('vote', function (Blueprint $table) {
    $table->id();
    $table->unsignedBigInteger('post_id');
    $table->unsignedBigInteger('user_id');
    $table->timestamps();

    $table->foreign('post_id')->references('id')->on('post');
    $table->foreign('user_id')->references('id')->on('users');

});

Schema::table('post', function(Blueprint $table) {
    $table->foreign('vote_id')->references('id')->on('vote');
});

Upvotes: 1

Chukwuezugo Okpara
Chukwuezugo Okpara

Reputation: 62

Its important to note that migration files run in order of creation. So go through your table design and get those independent ones first(change their file name i.e timestamp so they come first), then followed by the dependent(ones that have the foreign keys to the independents). I hope you understand?

Upvotes: 0

Related Questions