I've got a migration I've been banging my head against for a few days throwing a general error: 1005 and I've checked other highly rated questions on SO like this to make sure I'm not missing something simple, as well as I talked with several people on the offical Laravel discord.
When I try and run the migration I get the following error:
$ php artisan migrate
Migrating: 2019_12_23_200550_create_api_user_property_table
Illuminate\Database\QueryException : SQLSTATE[HY000]: General error: 1005 Can't create table `manageadmin`.`api_user_property` (errno: 150 "Foreign key constraint is incorrectly formed") (SQL: alter table `api_user_property` add constraint `api_user_property_property_id_foreign` foreign key (`property_id`) references `properties` (`id`) on delete cascade)
at /vendor/laravel/framework/src/Illuminate/Database/Connection.php:664
660| // If an exception occurs when attempting to run a query, we'll format the error
661| // message to include the bindings with SQL, which will make this exception a
662| // lot more helpful to the developer instead of just the database's errors.
663| catch (Exception $e) {
> 664| throw new QueryException(
665| $query, $this->prepareBindings($bindings), $e
666| );
667| }
668|
Exception trace:
1 Doctrine\DBAL\Driver\PDOException::("SQLSTATE[HY000]: General error: 1005 Can't create table `manageadmin`.`api_user_property` (errno: 150 "Foreign key constraint is incorrectly formed")")
/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:123
2 PDOException::("SQLSTATE[HY000]: General error: 1005 Can't create table `manageadmin`.`api_user_property` (errno: 150 "Foreign key constraint is incorrectly formed")")
vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:121
Please use the argument -v to see more details.
Normally I'd expect migration order issues, or mismatched datatypes, but the properties table was created back in 2014 and the datatypes match exactly as far as I can see.
2014_11_06_064216_create_properties_table.php
<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
class CreatePropertiesTable extends Migration {
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('properties', function(Blueprint $table)
{
$table->increments('id');
$table->string('name');
$table->integer('organization_id')->unsigned()->index();
$table->foreign('organization_id')->references('id')->on('organizations')->onDelete('cascade')->onUpdate('cascade');
$table->timestamps();
});
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::drop('properties');
}
}
2019_12_23_200550_create_api_user_property_table.php
<?php
use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;
class CreateApiUserPropertyTable extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('api_user_property', function (Blueprint $table) {
$table->increments('id');
$table->unsignedBigInteger('api_user_id')->index();
$table->foreign('api_user_id')->references('id')->on('api_users')->onDelete('cascade');
$table->unsignedInteger('property_id')->index();
$table->foreign('property_id')->references('id')->on('properties')->onDelete('cascade');
$table->timestamps();
$table->engine = 'InnoDB';
});
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::dropIfExists('api_user_property');
}
}
This migration is creating a pivot table between the ApiUser model and the Property model, there was another migration also back in 2014 that created a pivot table between the Property model and the User model that created the same foreign key and worked properly.
2014_11_06_064259_create_property_user_table.php
<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
class CreatePropertyUserTable extends Migration {
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('property_user', function(Blueprint $table)
{
$table->increments('id');
$table->integer('property_id')->unsigned()->index();
$table->foreign('property_id')->references('id')->on('properties')->onDelete('cascade');
$table->integer('user_id')->unsigned()->index();
$table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
$table->timestamps();
});
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::drop('property_user');
}
}
I've even manually checked in the db to make sure the properties.id and api_user_property.property_id datatypes matched.
MariaDB [manageadmin]> describe properties;
+---------------------+------------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+------------------+------+-----+---------------------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(255) | NO | | NULL | |
| organization_id | int(10) unsigned | NO | MUL | NULL | |
| created_at | timestamp | NO | | current_timestamp() | |
| updated_at | timestamp | NO | | current_timestamp() | |
| site_url | varchar(255) | YES | | NULL | |
| escalation_interval | int(11) | NO | MUL | 15 | |
| timezone | varchar(255) | NO | | "America/Denver | |
| hotel_id | int(10) unsigned | YES | MUL | NULL | |
| siteid | varchar(255) | YES | | NULL | |
| contact_email | varchar(255) | YES | | NULL | |
+---------------------+------------------+------+-----+---------------------+----------------+
11 rows in set (0.005 sec)
MariaDB [manageadmin]> describe api_user_property;
+-------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| api_user_id | bigint(20) unsigned | NO | MUL | NULL | |
| property_id | int(10) unsigned | NO | | NULL | |
| created_at | timestamp | YES | | NULL | |
| updated_at | timestamp | YES | | NULL | |
+-------------+---------------------+------+-----+---------+----------------+
5 rows in set (0.005 sec)
What's really puzzling is if I run php artisan migrate:fresh the migration works exactly as is and creates the foreign key, but of course I can't drop all the data in my tables.
So I believe I've addressed the common issues preventing a foreign key from being created.
- Column Datatypes match including unsigned attribute
- Migration Order is correct
- Table to be created is InnoDB
- Tried with and without the
->index()on$table->unsignedInteger('property_id')
I'm stumped. Can anyone offer any insight as to what I've missed here or how to fix this migration?
