Adding a foreign key fails with 'Can't create table'

Question

I don't understand what is wrong with my migration setup. I am getting this error when I try to add a foreign key for an already existing table.

Migrating: 2019_11_27_201351_add_foreign_key_to_tags

Illuminate\Database\QueryException  : SQLSTATE[HY000]: General error: 1005 Can't create table `tip`.`tags` (errno: 150 "Foreign key constraint is incorrectly formed") (SQL: alter table `tags` add constraint `tags_website_id_foreign` foreign key (`website_id`) references `websites` (`id`))

My table that I try to create a foreign key on

public function up()
{
    Schema::create('tags', function (Blueprint $table) {
        $table->bigIncrements('id');
        $table->string('tagname')->nullable();
        $table->integer('website_id')->unsigned();
        $table->string('tag_category');
        $table->timestamps();
    });
}

Here is the table I try to reference:

public function up()
{
    Schema::create('websites', function (Blueprint $table) {
        $table->bigIncrements('id');
        $table->string('websiteName')->nullable();
        $table->timestamps();
    });
}

And here's the migration that I'm calling

public function up()
{
    Schema::table('tags', function (Blueprint $table) {
        //
        $table->foreign('website_id')->references('id')->on('websites');
    });
}

This migration is called after all the tables are created. Any help is appreciated

Similar problem was found here: https://stackoverflow.com/questions/32669880/laravel-migration-foreign-key-constraint-is-incorrectly-formed-errno-150 — kamil-kubicki, Nov 27 '19 at 20:35

score 0 · Answer 1 · answered Nov 27 '19 at 20:35

0

Okay, I have found the solution. When creating tags table, instead of $table->integer('website_id')->unsigned();

I needed to use $table->unsignedBigInteger('website_id');

After changing this, the migrations ran successfully

answered Nov 27 '19 at 20:35

Matrix

437
5
18

Adding a foreign key fails with 'Can't create table'

1 Answers1