Cannot add foreign key constraint

Question

    Schema::create('posts', function (Blueprint $table) {
                $table->increments('id');
                $table->string('title');
                $table->text('body');
                $table->string('image')->nullable();
                $table->integer('user_id')->unsigned();
                $table->integer('category_id');
                $table->timestamps();

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

Schema::create('categories', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name')->unique();
            $table->timestamps();    
        });

----> SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint (SQL: alter table posts add constraint posts_category_id_foreign foreign key (category_id) references categories (id) on delete cascade)

they are created sequentially, so is first created posts and then categories, the order should be switched, or the constrain added in a migration at the bottom — Alberto Sinigaglia
Did my answer help you or do you still have problems with the question? — mrhn

mrhn mrhn · Accepted Answer · 2020-06-07T22:45:18

Categories needs to exist before the foreign key is created, as you can not foreign key to a table that does not exists yet.

Schema::create('categories', function (Blueprint $table) {
    ...
});

Schema::create('posts', function (Blueprint $table) {
    ...
});

Secondly when you do a foreign key, you will have to have the id as the same type as the tables foreign key. When you do increments('id') it will actually create an unsigned integer, therefor your category_id in posts should be an unsigned integer.

Schema::create('posts', function (Blueprint $table) {
    $table->unsignedInteger('category_id');

    $table->foreign('category_id')->references('id')->on('categories')->onDelete('cascade');
});

Cannot add foreign key constraint

1 Answers