4

The Problem

I want to add foreign keys to tables. When I run my first migration create_posts_table that looks like this:

Schema::create('posts', function(Blueprint $table) {
    $table->engine = 'InnoDB';

    $table->increments('id');
    $table->unsignedInteger('user_id')->index();

    // . . .
});

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

The following error is thrown:

[Illuminate\Database\QueryException] SQLSTATE[HY000]:

General error: 1215 Cannot add foreign key constraint (SQL: alter table posts add constraint posts_user_id_foreign foreign key (user_id) references users (id) on delete cascade)

This is caused because the users table is not created yet, hence the failure to create the users' referencing foreign key on the posts table.

Possible Solution

The solution to this problem would be to add the foreign keys with a new migration after all of the tables had been created. However, it seems clunky to me.

The question

How can I define the foreign keys inside their respective tables' migrations, instead of adding them separately with the different migration after all of the tables had been created?

Community
  • 1
  • 1
Alex Lomia
  • 6,705
  • 12
  • 53
  • 87

2 Answers2

3

You can perform multiple migrations in the same migration file. If you have a posts table where you want a foreign key to the users table, but the users table does not yet exist, you either have to do it in the users table migration file after the users table has been created - or you have to do a separate migration, like you said. You can't "save" instructions for later in migrations.

Joel Hinz
  • 24,719
  • 6
  • 62
  • 75
  • So, these are the only options? – Alex Lomia Jun 04 '16 at 11:19
  • Afraid so, as far as I'm aware. – Joel Hinz Jun 04 '16 at 11:20
  • Thanks for an answer – Alex Lomia Jun 04 '16 at 11:25
  • Just for clarity, migrations are run in the order in which they are created. You will see each migration file has a date/time in the beginning of the filename. Setup your foreign keys in the migration file thats setup after the required tables are created; create a separate migration after all created table migrations are setup, or re-order the timestamps associated with your migration files (though this is not recommended - rather start fresh). – SupaMonkey Aug 25 '18 at 08:05
0

In laravel way is keeping separate migration files for different tables with indexing, primary key & foreign keys.....

CreateUsersTable

class CreateUsersTable extends Migration
{

    public function up()
    {
        Schema::create('users', function (Blueprint $table) {
        $table->increments('id');
        $table->string('email');
        $table->string('password', 60);            
        $table->enum('status', ['0', '1'])->default('0');
        $table->rememberToken();
        $table->nullableTimestamps();

        $table->unique('email');

        });
    }

    public function down()
    {
        Schema::drop('users');
    }
}

CreatePostsTable

class CreatePostsTable extends Migration
{

    public function up()
    {
        Schema::create('posts', function (Blueprint $table) {
            $table->increments('id');
            $table->integer('user_id')->unsigned();         

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

    public function down()
    {
        Schema::drop('posts');
    }
}
Shanka SMS
  • 644
  • 6
  • 15