laravel 5.4 migration errno: 150 "Foreign key constraint is incorrectly formed"

Question

have 4 migrations as shown below. this is users table.

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

and this is artists migration.

Schema::create('artists', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name');
            $table->string('slug');
            $table->string('image')->nullable();
            $table->text('biography')->nullable();
            $table->integer('week_hits');
            $table->timestamp('week_date');
            $table->timestamp('viewed_now');
            $table->boolean('status')->default(1);
            $table->integer('user_id')->unsigned();
            $table->foreign('user_id')->references('id')->on('users');
            $table->timestamps();
        });

this is songs migration.

Schema::create('songs', function (Blueprint $table) {
            $table->increments('id');
            $table->string('title');
            $table->text('lyrics')->nullable();
            $table->string('mp3');
            $table->string('youtube_id')->nullable();
            $table->timestamp('week_date');
            $table->integer('week_hits')->nullable();
            $table->timestamp('played_now')->nullable();
            $table->timestamp('hits');
            $table->integer('album_id')->unsigned()->nullable();
            $table->foreign('album_id')->references('id')->on('albums');
            $table->integer('artist_id')->unsigned();
            $table->foreign('artist_id')->references('id')->on('artists');
            $table->timestamps();
        });
    }

and this is albums migration.

Schema::create('albums', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name');
            $table->string('cover');
            $table->integer('artist_id')->unsigned();
            $table->foreign('artist_id')->references('id')->on('artists');
            $table->boolean('status')->default(true);
            $table->timestamp('viewed_now')->nullable();
            $table->integer('week_hits')->nullable();
            $table->timestamp('week_date');
            $table->timestamps();
        });

and this is featuring which connects many to many , artists and songs.

Schema::create('featuring', function (Blueprint $table) {
    $table->integer('artist_id')->unsigned()->nullable();
    $table->foreign('artist_id')->references('id')->on('artists');
    $table->integer('song_id')->unsigned()->nullable();
    $table->foreign('song_id')->references('id')->on('songs')->onDelete('cascade');
    $table->timestamps();
});

when i try to migrate these four migrations i get this error.

SQLSTATE[HY000]: General error: 1005 Can't create table `laravel`.`#sql-f0_11e` (errno: 150 "Foreign key constraint is incorrectly formed") (SQL: alter table `songs` add const
  raint `songs_album_id_foreign` foreign key (`album_id`) references `albums` (`id`))

If you remove nullable() from $table->integer('album_id')->unsigned()->nullable(); Does it work? Do you get a difference error? — Ravi Gehlot, Jun 11 '17 at 04:14
https://stackoverflow.com/questions/22615926/migration-cannot-add-foreign-key-constraint-in-laravel look at this, may help you — Sagar Gautam, Jun 11 '17 at 04:17
It seems you have problem with order of migrations. Tables with foreign keys should be created after the tables they reference to have been created. Please check the orders in which tables are created. — Sagar Gautam, Jun 11 '17 at 04:20

score 5 · Accepted Answer · answered Jun 11 '17 at 04:28

5

You have created table songs at first and then you have created table albums. When you try to add foreign key album_id at songs table, albums table hasn't been created so that you can't add foreign key to a table with out creation of that table.

So, What you need to is, Create albums table before songs table.

answered Jun 11 '17 at 04:28

Sagar Gautam

9,049
6
53
84

@sohaieb Glad to help ;) – Sagar Gautam Oct 17 '19 at 10:43

score 1 · Answer 2 · answered Jun 11 '17 at 04:20

You may not be able to set the foreign key during creation. You would have to create the table then set the foreign key. You can use Schema::create to create the table then use Schema::table to set the foreign key. Example:

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

Schema::table('posts', function (Blueprint $table) {
    $table->integer('user_id')->unsigned();

    $table->foreign('user_id')->references('id')->on('users');
});

laravel 5.4 migration errno: 150 "Foreign key constraint is incorrectly formed"

2 Answers2