Laravel Migration Two table foreign key dependencies

Question

I have two tables (employees and also regions),

I am trying to create it using migration, unfortunately it wont let me, I am getting :

[Illuminate\Database\QueryException]
SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint (SQL: alter table regions` add constraint regions_create_by_foreign foreign key (`create_by`) references `employees` (`id`))

[PDOException]
SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint

Below is my migration code:

FILE: 2014_10_11_052414_create_regions_table.php  //this is the migration file for regions

    Schema::create('regions', function(Blueprint $table)
    {
        $table->increments('id');
        $table->string('name',50)->unique();
        $table->integer('head_office_flag');
        $table->integer('create_by')->unsigned();
        $table->foreign('create_by')->references('id')->on('employees'); //this is the problem
        $table->datetime('create_datetime')->default(DB::raw('CURRENT_TIMESTAMP'));
    });

FILE: 2014_10_12_110447_create_employees_table.php //this is the migration file for employees

    Schema::create('employees', function(Blueprint $table)
    {
        $table->increments('id');
        $table->string('first_name',50);
        $table->string('surname',50);
        $table->string('email',100);
        $table->integer('region_id');   
        $table->string('photos',255)->nullable();
        $table->string('mobile_no',50);
        $table->string('office_no',50)->nullable();
        $table->string('landline_no',50)->nullable();
        $table->integer('create_by')->unsigned();                    
        $table->foreign('create_by')->references('id')->on('employees'); //this is the problem to reference to itself
        $table->datetime('create_datetime')->default(DB::raw('CURRENT_TIMESTAMP'));
        $table->integer('status');
    });

My questions are:

• do I need to alter the sequence of migration? considering both have foreign key to one another. *Note, I have tried changing the sequence by renaming the file and using earlier timestamp in the file name

• what additional code should I add?

• do I have to add additional code in my seeder?

Thanks in advance! Cheers.

UPDATE

@Marcin: I have updated it to:

FILE: 2014_10_11_052414_create_regions_table.php

    Schema::create('regions', function(Blueprint $table)
    {
        $table->increments('id');
        $table->string('name',50)->unique();
        $table->integer('head_office_flag');
        $table->integer('create_by')->unsigned();
        $table->datetime('create_datetime')->default(DB::raw('CURRENT_TIMESTAMP'));
    });

FILE: 2014_10_11_110447_create_employees_table.php

    Schema::create('employees', function(Blueprint $table)
    {
        $table->increments('id');
        $table->string('first_name',50);
        $table->string('surname',50);
        $table->string('email',100);
        $table->integer('region_id')->unsigned();
        $table->string('photos',255)->nullable();
        $table->string('mobile_no',50);
        $table->string('office_no',50)->nullable();
        $table->string('landline_no',50)->nullable();
        $table->integer('create_by')->unsigned();
        $table->datetime('create_datetime')->default(DB::raw('CURRENT_TIMESTAMP'));
        $table->integer('status');
    });

FILE: 2014_10_12_065402_alter_regions_table_FK.php

    Schema::table('regions', function($table)
    {
        $table->foreign('create_by')->references('id')->on('employees');
    });

FILE: 2014_10_12_070219_alter_employees_table_FK.php

    Schema::table('employees', function($table)
    {
        $table->foreign('region_id')->references('id')->on('regions');
        $table->foreign('create_by')->references('id')->on('employees');
    });

I am still getting an error:

![Error][2]

Answer 1

If you have 2 tables reffering to each other (or foreign key in table reffering to the same table) you should do:

1. create migration for 1st table - you don't use here any foreign keys
2. create migration for 2nd table - you don't use here any foreign keys
3. create migration for 1st table - here you add only foreign keys
4. create migration for 2nd table - here you add only foreign keys

You can of course merge for example 3rd and 4th step but probably it's better to separate them for readability