Why does Laravel give an error when migrating from a foreign key?

When migrating (php artisan migrate) with a foreign-key, an error occurs:

SQLSTATE[HY000]: General error: 1005 Can't create table slien_test.users (errno: 150 "Foreign key constraint is incorrectly formed") (SQL: alter table users add constraint users_city_id_foreign foreign key (city_id) references cities (id))

I tried to make a connection between the entity user and its city - city.

Migration create_users_table:

public function up()
{
    Schema::create('users', function (Blueprint $table) {

        $table->id();
        $table->string('first_name');
        $table->string('last_name');
        $table->string('email')->unique();
        $table->string('password');
        $table->string('phone')->unique()->nullable();

        $table->unsignedBigInteger('city_id')->nullable();
        $table->foreign('city_id')->references('id')->on('cities');

        // new syntax
        // $table->foreignId('city_id')->constrained('cities');

        $table->timestamp('email_verified_at')->nullable();
        $table->rememberToken();
        $table->timestamps();
        $table->softDeletes('removed_at', 0);
    });
}

public function down()
{
    Schema::table('users', function (Blueprint $table) {
        $table->dropForeign('city_id');
    })
    Schema::dropIfExists('users');
}

Migration create_cities_table:

public function up()
{
    Schema::create('cities', function (Blueprint $table) {
        $table->id();
        $table->string('city');
    });
}

public function down()
{
    Schema::dropIfExists('cities');
}

As a result a long search for the cause of the error was not successful. At the same time, I tried both the new syntax and the old one (according to the documentation). Made an additional migration add_foreign_key_in_users_table:

public function up()
{
    Schema::table('users', function (Blueprint $table) {
        $table->unsignedBigInteger('city_id')->nullable();
        $table->foreign('city_id')->references('id')->on('cities');
    });
}

public function down()
{
    Schema::dropForeign('city_id');
}

After the php artisan migrate command, the migrations were completed successfully. I assume that the error crashes because the table referenced by the link in the users table is missing. But, why then does no one take out foreign-keys in other migrations? Everyone writes in the same migration and there are no errors. How to solve the problem with this a mistake?

UPD:

users table

users_table

cities table

cities_table

Author: Daniil Kedrov, 2020-05-26

2 answers

What is the order of migrations? First, the migration should be performed for cities, and then users with links. To do this, change the timestamp in the file name. Or create a migration file users after the cities.

 1
Author: E_K, 2020-05-26 23:13:37
$table->unsignedBigInteger('city_id')->nullable();
$table->foreign('city_id')->references('id')->on('cities');

// new syntax
// $table->foreignId('city_id')->constrained('cities_table');

Write why this is all, and it depends on the version if it is more recent than 5.8, then it is enough to register it in the migration:

$table->integer('city_id')->nullable();

The rest is written in the model - this is the relationship according to your architecture, for example:

public function city()
    {
        return $this->belongsTo('App\City');
    }
 0
Author: imvasha, 2020-05-26 22:03:29