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 tableusers
add constraintusers_city_id_foreign
foreign key (city_id
) referencescities
(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
cities
table
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.
$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');
}