Corbin's avatar

SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint, in dev env, but works fine in testing

I'm trying to migrate my messages/conversations/conversation_participants on a recently installed laravel project. The code works fine in testing, but doesn't work when I run php aritsan migrate

Error

SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint (SQL: alter table messages add constraint messages_conversation_id_foreign foreign key (conversation_id) references conversations (id))

2020_07_13_000145_create_messages_table

Schema::create('messages', function (Blueprint $table) {
    $table->bigIncrements('id');
    $table->unsignedBigInteger('sender_id');
    $table->unsignedBigInteger('conversation_id');
    $table->text('body');
    $table->timestamps();

    $table->foreign('sender_id')->references('id')->on('users');
    $table->foreign('conversation_id')->references('id')->on('conversations');
});

2020_07_13_053229_create_conversations_table

Schema::create('conversations', function (Blueprint $table) {
    $table->bigIncrements('id');
    $table->string('hashed_id')->nullable();
    $table->unsignedInteger('has_reply')->unsigned()->default(0);
    $table->text('subject');
    $table->timestamps();
});

2020_07_14_045926_create_conversation_participants_table

Schema::create('conversation_participants', function (Blueprint $table) {
    $table->integer('user_id')->unsigned();
    $table->unsignedBigInteger('conversation_id');
    $table->tinyInteger('status')->default(0);
    $table->tinyInteger('is_sender')->default(0);

    $table->foreign('conversation_id')->references('id')->on('conversations')->onDelete('cascade');
    $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
});
0 likes
3 replies
MichalOravec's avatar
MichalOravec
5 years ago
Best Answer
Level 75

Rename migration file

2020_07_13_053229_create_conversations_table

to

2020_07_13_000140_create_conversations_table

Because you have to first create conversations table and then you can use it as foreign key in the messages table.

Everytime order of migration files is very important.

warpig's avatar

@MichalOravec I have something very similar, have tried to change 2021_11_25_211939_create_profiles_table.php to 2021_26_170638_create_profiles_table.php

2021_11_25_211939_create_profiles_table.php <-- november 25th

        Schema::create('profiles', function (Blueprint $table) {
            $table->id();
            $table->unsignedBigInteger('user_id');
            $table->string('full_name');
            $table->string('bio');
            $table->string('linkedin_url')->nullable();
            $table->string('optional')->nullable();
            $table->timestamps();

            $table->foreign('user_id')
                ->references('id')
                ->on('users')
                ->onDelete('cascade');
        });

2021_11_26_170643_create_qualities_table <-- november 26

        Schema::create('qualities', function (Blueprint $table) {
            $table->id();
            $table->unsignedBigInteger('profile_id');
            $table->string('quality');
            $table->timestamps();

            $table->foreign('profile_id')
                ->references('id')
                ->on('profile')
                ->onDelete('cascade');
        });

Please or to participate in this conversation.