Level 65
Have you confirmed that both IDs match and it's in the users table?
They must be the same type and unsigned as well.
6 likes
Apr 8, 2015
21
Level 3
SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint
I've been following along Jeff's tutorial "Laravel 5 Fundamentals".
I'm at video 14, "Eloquent Relationships", and trying to do a migration but keep getting this error:
[Illuminate\Database\QueryException]
SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint (SQL: alter table `articles` add constraint articles_user_id_foreign foreign key (`user_id`) references `users` (`id`) on delete cascade)
This is my users migration file
<?php
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;
class CreateUsersTable extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('users', function(Blueprint $table)
{
$table->increments('id');
$table->string('name');
$table->string('email')->unique();
$table->string('password', 60);
$table->rememberToken();
$table->timestamps();
});
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::drop('users');
}
}
this is my articles migration
<?php
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;
class CreateArticlesTable extends Migration {
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('articles', function(Blueprint $table)
{
$table->increments('id');
$table->integer('user_id')->unsigned();;
$table->string('title');
$table->text('body');
$table->timestamps();
$table->timestamp('published_at');
});
Schema::table('articles', function($table){
$table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
});
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::drop('articles');
}
}
I'm a beginner, so sorry if my question is too basic, but I tried a few solutions before coming here for help. :D
Anyone has any pointers for me?
Best,
Gabriel
Level 6
@gabingm make sure you have table engine set to InnoDB by default or set it explicitly with $table->engine = 'InnoDB';
4 likes
Level 3
Level 65
@constb Does that actually cause this?
Level 1
@bashy yes, only the InnoDB engine handles foreign key constraints.
2 likes
Level 65
So it is, I forget the differences between MyISAM and InnoDB. Just seen it here: http://dev.mysql.com/doc/refman/5.6/en/myisam-storage-engine.html
Level 4
A bit off-topic, I was having same error message, but caused by use different columns type, like: if user_id is integer and id (on user) is big integer.
6 likes
Level 1
On my case i have miss spelled the on table
Level 4
A bit off topic too, but I had the same error when I forgot to unsigned my columns that were my foreign keys.
// SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint $table->integer('user_id');
// No error $table->integer('user_id')->unsigned();
6 likes
Level 2
I ran into this as well. Like @ShawnDL above, I was using increments which is unsigned-int.
$table->increments('id' );
And in my cross-reference, I mistakenly used int. Once I corrected with
->unsigned();
It resolved issues.
5 likes
Level 1
I got the same error...and in fact..the problem was that i created the model along with the migration file before creating the master (containing the primary keys) tables....I deleted the model and created it again....problem solved.....
Level 3
please can someone help me out
i am getting this error and i tried to sort it out seems difficult
[Illuminate\Database\QueryException]
SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint (SQL: alter table companies add constraint companies_use r_id_foreign foreign key (user_id) references users (id))
[PDOException] SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint
1 like
Level 1
big thanks to @bashy for the int type notice.
Level 9
For those still stuck, change the migration order. I mean, make sure that the table you're referencing exists before being referenced.
So if you're editing the books table schema by referencing an id on the authors table, make sure the authors table is created before the books table. Hope it helps :)
Level 18
One more thing to keep in mind is that if you are using onDelete('set null') or onUpdate('set null'), then the migrations will work only when the foreignKey is set to be nullable.
$child->foreign('parent_id')->references('id')->on ('parens')->onDelete('set null');
//Will work only if .
$table->unsignedInteger('parent_id')->nullable();
//Otherwise it will give error when running the migration
2 likes
Level 10
@RMFF - Just had the exact same issue! Thanks for pointing that out! 👏👍🍻
Level 1
@neeravp This was the issue with my migration, thanks.
Level 11
THANK YOU!
The problem for me is I was adding a foreign_key but there was already data in the table. Adding nullable to the relationship thing_id fixed it.
Level 1
In my case, my users table was like MyISAM and the tables with relationship were going by default InnoDB, generating the error ,Cannot add foreign key constraint, MyISAM doesn't support foreign keys, check first of all which mysql engine is being used in tables. I solved my problems leaving the tables with the same innoDB engine. and adding $table->unsignedBigInteger('user_id');
!! MyISAM not support foreign key relationship
My laravel version 5.8.27
- Leave all tables as the same mysql innoDB engine
- $table->unsignedBigInteger('user_id');
- $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
Level 5
There are 2 issues with this problem. 1: When referencing a foreign key make sure the key is bigInteger and unsigned(). The second issue relates two timings. This means that it is finding difficulty adding a foreign key to a table that yet exist. If you look in the migrations you will see that they contain a timestamp. The trick is to modify the referenced table's timestamp so that it is created before the table that references it. This can be done with a mv.
For me personally, I solved this problem by making sure the foreign key was referencing the same types (as in bigInteger) and also altering the timestamp so that the referenced table was created first.
Laravel always creates a user table. Now suppose I have a table named Questions and a Table named Answers. Now a question can have many answers, so my question table contains a foreign key reference to the answers table. This looks like this:-
public function up() { Schema::create('questions', function (Blueprint $table) { $table->id(); $table->bigInteger('answer_id')->unsigned(); $table->text('question'); $table->timestamps(); $table->foreign('answer_id')->references('id')->on('answers')->onDelete('cascade'); }); }
And in my create answers table
public function up() { Schema::create('answers', function (Blueprint $table) { $table->id(); $table->text('answer'); $table->timestamps(); }); }
Now if I do an ls-alt we will see that the answers table is created before the Questions table (based on the Timestamp).
2014_10_12_000000_create_users_table.php 2014_10_12_100000_create_password_resets_table.php 2019_08_19_000000_create_failed_jobs_table.php 2022_06_21_140230_create_answers_table.php 2022_06_21_140231_create_questions_table.php
By renaming my answers_table to 140230 it gets created before the questions thereby preventing the above error from occuring.
Level 1
removed the "$table->engine = 'InnoDB';" declration from my migration and it worked.
Please or to participate in this conversation.