General error: 1215 Cannot add foreign key constraint, Laravel 5 & MySQL

Question

In a blank Laravel project, I want to create foreign key constraint between users and questions, where users table will hold build-in Laravel User, but Question will be a custom model.

After running php artisan migrate the follownign error occurs:

   Illuminate\Database\QueryException  : SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint (SQL: alter table `questions` add constraint `questions_user_id_foreign` foreign key (`user_id`) references `users` (`id`) on delete cascade)
  at /home/artur/Exposit/EDU/PHP/lara/vendor/laravel/framework/src/Illuminate/Database/Connection.php:664
    660|         // If an exception occurs when attempting to run a query, we'll format the error
    661|         // message to include the bindings with SQL, which will make this exception a
    662|         // lot more helpful to the developer instead of just the database's errors.
    663|         catch (Exception $e) {
  > 664|             throw new QueryException(
    665|                 $query, $this->prepareBindings($bindings), $e
    666|             );
    667|         }
    668|

Here is create_users_table migration generated by laravel:

 Schema::create('users', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->string('name');
            $table->string('email')->unique();
            $table->timestamp('email_verified_at')->nullable();
            $table->string('password');
            $table->rememberToken();
            $table->timestamps();
        });

And here is my migration:

Schema::create('questions', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->string('title');
            $table->string('slug')->unique();
            $table->text('body');
            $table->unsignedInteger('views')->default(0);
            $table->unsignedInteger('answers')->default(0);
            $table->integer('votes')->default(0);
            $table->unsignedInteger('best_answer_id')->nullable();
            $table->unsignedInteger('user_id');
            $table->timestamps();
        });

        Schema::table('questions', function (Blueprint $table) {
            $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
        });

I've tried to separate questions table creation and altering with foreign key constraint into two migrations, but got the same error. Note, that non of related answers on stackoverflow were helpful to me.

Sandeep Kaur · Accepted Answer · 2019-04-18 12:55:20Z

14

Laravel 5.8 Added bigIncrements As Defaults

So there is mismatch in foreign key field types. You see bigIncrements(id) in User table and unsigned Integer(user_id) in questions table.

How to fix

Either change original migration from bigIncrements() to just
increments()
Or in your foreign key column do unsignedBigInteger() instead of unsignedInteger().

answered Apr 18, 2019 at 12:55

Sandeep Kaur

4374 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tyler Durden Over a year ago

Solved my problem by using unsignedBigInteger type. Thanks a lot!

Emtiaz Zahid · Accepted Answer · 2019-04-18 11:46:42Z

In user table bigIncrements('id') making primary key which is unsigned.

and its type is big integer

while you setting a foreign key its also should be unsigned.

add unsigned() function and change the type from unsignedInteger to bigInteger

Example:

 Schema::create('questions', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->string('title');
            $table->string('slug')->unique();
            $table->text('body');
            $table->unsignedInteger('views')->default(0);
            $table->unsignedInteger('answers')->default(0);
            $table->integer('votes')->default(0);
            $table->unsignedInteger('best_answer_id')->nullable();
            $table->bigInteger('user_id')->unsigned();
            $table->timestamps();
            $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
        });

Dharman · Accepted Answer · 2021-04-18 05:58:34Z

How I fixed it For Laravel 8: All the migrations generated in L8 is using just $table->id(); which is bigInteger. Make sure on your second table which is gonna contain foreignkey, you have $table->unsignedBigInteger('post_id');

First set the correct type:

posts table

Schema::create('posts', function (Blueprint $table) {
 $table->id(); //note by default in laravel 8 this is bigint
 $table->string('title')->nullable();
 $table->timestamps();
});

comments table

Schema::create('comments', function (Blueprint $table) {
 $table->id();
 $table->string('description')->nullable();
 //foriegn key set up below
 $table->unsignedBigInteger('post_id');
 $table->foreign('post_id')->references('id')->on('posts')->onDelete('cascade');

});

Secondly re-order the migration files (the posts migration should be run first before you can run the comments file as laravel cannot find the id of the post to create the foreign key)

2021_04_10_043454_create_posts_table -> 04/10 (earlier date will run first) 2021_04_14_050712_create_posts_table -> 04/14 (later date runs after)

lastly, clear cache first php artisan cache:clear then run php artisan migrate

Dev · Accepted Answer · 2019-04-18 11:41:19Z

Data types of primary and foreign keys must be the same.

Please update questions migration to:

Schema::create('questions', function (Blueprint $table) {
    $table->bigIncrements('id');
    $table->string('title');
    $table->string('slug')->unique();
    $table->text('body');
    $table->unsignedInteger('views')->default(0);
    $table->unsignedInteger('answers')->default(0);
    $table->integer('votes')->default(0);
    $table->unsignedInteger('best_answer_id')->nullable();
    $table->bigInteger('user_id')->unsigned()->nullable();
    $table->timestamps();
});

Schema::table('questions', function (Blueprint $table) {
    $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
});

Do the same for best_answer_id too if it is also going to be a foreign key.

saber tabatabaee yazdi · Accepted Answer · 2020-06-27 16:06:11Z

0

change in migration source table

Schema::create('table_name', function (Blueprint $table) {
$table->BigIncrements('id');

on

Schema::create('table_name', function (Blueprint $table) {
$table->increments('id');

answered Jun 27, 2020 at 16:06

saber tabatabaee yazdi

4,9953 gold badges53 silver badges63 bronze badges

Collectives™ on Stack Overflow

General error: 1215 Cannot add foreign key constraint, Laravel 5 & MySQL

5 Answers 5

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related