Edit: solution was easier than I though, migrations happen in alphabetical order, my categories migrations was created after my posts migration, meaning the categories.id
column doesn't exist yet when the foreign key is added, resulting in the error. Changing the name so the categories table is created first solved the problem.
To try and understand Laravel a bit better, I decided to make a bog-standard blog website. I have three models, User, Post and Category, and I tried setting up some relations in the posts migration:
public function up()
{
Schema::create('posts', function (Blueprint $table) {
$table->increments('id');
$table->integer('user_id')->unsigned()->index();
$table->integer('category_id')->unsigned()->nullable();
$table->string('title');
$table->text('body');
$table->timestamps();
$table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
$table->foreign('category_id')->references('id')->on('categories')->onDelete('set null');
});
}
The first relation works, but the second one errors because of onDelete('set null')
with the error Cannot add foreign key constraint (SQL: alter table 'posts' add constraint 'posts_category_id_foreign' foreign key ('category_id') references 'categories' ('id') on delete set null)
. I've also tried adding the foreign keys like this, but I get the same error:
Schema::table('posts', function (Blueprint $table) {
$table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
$table->foreign('category_id')->references('id')->on('categories')->onDelete('set null');
});
However, if I remove the 2nd migration and manually add it in HeidiSQL, it works fine:
As far as I know, there's two things you have to do to use
set null
, making the column nullable (which I've done), and add the relation with onDelete('set null')
. Is onDelete('set null')
simply not supported in Laravel 5.5, or am I doing something wrong?
In case it matters, my categories migration:
public function up()
{
Schema::create('categories', function (Blueprint $table) {
$table->increments('id');
$table->string('name');
$table->timestamps();
});
}