General error 1215 cannot add foreign key constraint laravel - Исправление ошибок и поиск оптимальных решений проблем

I am running into the same problem here. I have a migration that adds a foreign key, referencing the id column on the users table and after upgrading to Laravel 5.8 this no longer works. This is the up of my original migration:

public function up()
{
    Schema::create('two_factor_auths', function (Blueprint $table) {
        $table->string('id')->nullable();
        $table->unsignedInteger('user_id');
        $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
        $table->timestamps();
    });
}

But then running php artisan migrate -v on a freshly created db (InnoDB) results in:

  IlluminateDatabaseQueryException  : SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint (SQL: alter table `two_factor_auths` add constraint `two_factor_auths_user_id_foreign` foreign key (`user_id`) references `users` (`id`) on delete cascade)

  at /home/vagrant/code/laravel-two-factor-authentication-example/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   PDOException::("SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint")
      /home/vagrant/code/laravel-two-factor-authentication-example/vendor/laravel/framework/src/Illuminate/Database/Connection.php:458

  2   PDOStatement::execute()
      /home/vagrant/code/laravel-two-factor-authentication-example/vendor/laravel/framework/src/Illuminate/Database/Connection.php:458

  3   IlluminateDatabaseConnection::IlluminateDatabase{closure}("alter table `two_factor_auths` add constraint `two_factor_auths_user_id_foreign` foreign key (`user_id`) references `users` (`id`) on delete cascade", [])
      /home/vagrant/code/laravel-two-factor-authentication-example/vendor/laravel/framework/src/Illuminate/Database/Connection.php:657

  4   IlluminateDatabaseConnection::runQueryCallback("alter table `two_factor_auths` add constraint `two_factor_auths_user_id_foreign` foreign key (`user_id`) references `users` (`id`) on delete cascade", [], Object(Closure))
      /home/vagrant/code/laravel-two-factor-authentication-example/vendor/laravel/framework/src/Illuminate/Database/Connection.php:624

  5   IlluminateDatabaseConnection::run("alter table `two_factor_auths` add constraint `two_factor_auths_user_id_foreign` foreign key (`user_id`) references `users` (`id`) on delete cascade", [], Object(Closure))
      /home/vagrant/code/laravel-two-factor-authentication-example/vendor/laravel/framework/src/Illuminate/Database/Connection.php:459

  6   IlluminateDatabaseConnection::statement("alter table `two_factor_auths` add constraint `two_factor_auths_user_id_foreign` foreign key (`user_id`) references `users` (`id`) on delete cascade")
      /home/vagrant/code/laravel-two-factor-authentication-example/vendor/laravel/framework/src/Illuminate/Database/Schema/Blueprint.php:97

  7   IlluminateDatabaseSchemaBlueprint::build(Object(IlluminateDatabaseMySqlConnection), Object(IlluminateDatabaseSchemaGrammarsMySqlGrammar))
      /home/vagrant/code/laravel-two-factor-authentication-example/vendor/laravel/framework/src/Illuminate/Database/Schema/Builder.php:264

  8   IlluminateDatabaseSchemaBuilder::build(Object(IlluminateDatabaseSchemaBlueprint))
      /home/vagrant/code/laravel-two-factor-authentication-example/vendor/laravel/framework/src/Illuminate/Database/Schema/Builder.php:165

  9   IlluminateDatabaseSchemaBuilder::create("two_factor_auths", Object(Closure))
      /home/vagrant/code/laravel-two-factor-authentication-example/vendor/laravel/framework/src/Illuminate/Support/Facades/Facade.php:237

  10  IlluminateSupportFacadesFacade::__callStatic("create")
      /home/vagrant/code/laravel-two-factor-authentication-example/vendor/michaeldzjap/twofactor-auth/src/database/migrations/2017_05_26_102832_create_two_factor_auths_table.php:21

  11  CreateTwoFactorAuthsTable::up()
      /home/vagrant/code/laravel-two-factor-authentication-example/vendor/laravel/framework/src/Illuminate/Database/Migrations/Migrator.php:360

  12  IlluminateDatabaseMigrationsMigrator::IlluminateDatabaseMigrations{closure}()
      /home/vagrant/code/laravel-two-factor-authentication-example/vendor/laravel/framework/src/Illuminate/Database/Migrations/Migrator.php:367

  13  IlluminateDatabaseMigrationsMigrator::runMigration(Object(CreateTwoFactorAuthsTable), "up")
      /home/vagrant/code/laravel-two-factor-authentication-example/vendor/laravel/framework/src/Illuminate/Database/Migrations/Migrator.php:178

  14  IlluminateDatabaseMigrationsMigrator::runUp("/home/vagrant/code/laravel-two-factor-authentication-example/vendor/michaeldzjap/twofactor-auth/src/database/migrations/2017_05_26_102832_create_two_factor_auths_table.php")
      /home/vagrant/code/laravel-two-factor-authentication-example/vendor/laravel/framework/src/Illuminate/Database/Migrations/Migrator.php:147

  15  IlluminateDatabaseMigrationsMigrator::runPending([])
      /home/vagrant/code/laravel-two-factor-authentication-example/vendor/laravel/framework/src/Illuminate/Database/Migrations/Migrator.php:96

  16  IlluminateDatabaseMigrationsMigrator::run([])
      /home/vagrant/code/laravel-two-factor-authentication-example/vendor/laravel/framework/src/Illuminate/Database/Console/Migrations/MigrateCommand.php:71

  17  IlluminateDatabaseConsoleMigrationsMigrateCommand::handle()
      /home/vagrant/code/laravel-two-factor-authentication-example/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php:32

  18  call_user_func_array([])
      /home/vagrant/code/laravel-two-factor-authentication-example/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php:32

  19  IlluminateContainerBoundMethod::IlluminateContainer{closure}()
      /home/vagrant/code/laravel-two-factor-authentication-example/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php:90

  20  IlluminateContainerBoundMethod::callBoundMethod(Object(IlluminateFoundationApplication), Object(Closure))
      /home/vagrant/code/laravel-two-factor-authentication-example/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php:34

  21  IlluminateContainerBoundMethod::call(Object(IlluminateFoundationApplication), [])
      /home/vagrant/code/laravel-two-factor-authentication-example/vendor/laravel/framework/src/Illuminate/Container/Container.php:580

  22  IlluminateContainerContainer::call()
      /home/vagrant/code/laravel-two-factor-authentication-example/vendor/laravel/framework/src/Illuminate/Console/Command.php:183

  23  IlluminateConsoleCommand::execute(Object(SymfonyComponentConsoleInputArgvInput), Object(IlluminateConsoleOutputStyle))
      /home/vagrant/code/laravel-two-factor-authentication-example/vendor/symfony/console/Command/Command.php:255

  24  SymfonyComponentConsoleCommandCommand::run(Object(SymfonyComponentConsoleInputArgvInput), Object(IlluminateConsoleOutputStyle))
      /home/vagrant/code/laravel-two-factor-authentication-example/vendor/laravel/framework/src/Illuminate/Console/Command.php:170

  25  IlluminateConsoleCommand::run(Object(SymfonyComponentConsoleInputArgvInput), Object(SymfonyComponentConsoleOutputConsoleOutput))
      /home/vagrant/code/laravel-two-factor-authentication-example/vendor/symfony/console/Application.php:901

  26  SymfonyComponentConsoleApplication::doRunCommand(Object(IlluminateDatabaseConsoleMigrationsMigrateCommand), Object(SymfonyComponentConsoleInputArgvInput), Object(SymfonyComponentConsoleOutputConsoleOutput))
      /home/vagrant/code/laravel-two-factor-authentication-example/vendor/symfony/console/Application.php:262

  27  SymfonyComponentConsoleApplication::doRun(Object(SymfonyComponentConsoleInputArgvInput), Object(SymfonyComponentConsoleOutputConsoleOutput))
      /home/vagrant/code/laravel-two-factor-authentication-example/vendor/symfony/console/Application.php:145

  28  SymfonyComponentConsoleApplication::run(Object(SymfonyComponentConsoleInputArgvInput), Object(SymfonyComponentConsoleOutputConsoleOutput))
      /home/vagrant/code/laravel-two-factor-authentication-example/vendor/laravel/framework/src/Illuminate/Console/Application.php:90

  29  IlluminateConsoleApplication::run(Object(SymfonyComponentConsoleInputArgvInput), Object(SymfonyComponentConsoleOutputConsoleOutput))
      /home/vagrant/code/laravel-two-factor-authentication-example/vendor/laravel/framework/src/Illuminate/Foundation/Console/Kernel.php:122

  30  IlluminateFoundationConsoleKernel::handle(Object(SymfonyComponentConsoleInputArgvInput), Object(SymfonyComponentConsoleOutputConsoleOutput))
      /home/vagrant/code/laravel-two-factor-authentication-example/artisan:37

Changing from $table->unsignedInteger('user_id'); to $table->bigIncrements('user_id'); in my migration makes it work. I can then successfully run the migration. Maybe this will help.

Источник

Chiming in here a few years after the original question, using laravel 5.1, I had the same error as my migrations were computer generated with all the same date code. I went through all the proposed solutions, then refactored to find the error source.

In following laracasts, and in reading these posts, I believe the correct answer is similar to Vickies answer, with the exception that you don’t need to add a separate schema call. You don’t need to set the table to Innodb, I am assuming laravel is now doing that.

The migrations simply need to be timed correctly, which means you will modify the date code up (later) in the filename for tables that you need foreign keys on. Alternatively or in addition, Lower the datecode for tables that don’t need foreign keys.

The advantage in modifying the datecode is your migration code will be easier to read and maintain.

So far my code is working by adjusting the time code up to push back migrations that need foreign keys.

However I do have hundreds of tables, so at the very end I have one last table for just foreign keys. Just to get things flowing. I am assuming I will pull those into the correct file and modify the datecode as i test them.

So an example: file 2016_01_18_999999_create_product_options_table. This one needs the products table to be created. Look at the file names.

 public function up()
{
    Schema::create('product_options', function (Blueprint $table) {
        $table->increments('id');
        $table->integer('product_attribute_id')->unsigned()->index();
        $table->integer('product_id')->unsigned()->index();
        $table->string('value', 40)->default('');
        $table->timestamps();
        //$table->foreign('product_id')->references('id')->on('products');
        $table->foreign('product_attribute_id')->references('id')->on('product_attributes');
        $table->foreign('product_id')->references('id')->on('products');


    });
}

/**
 * Reverse the migrations.
 *
 * @return void
 */
public function down()
{
    Schema::drop('product_options');
}

the products table: this needs to migrate first. 2015_01_18_000000_create_products_table

public function up()
{
    Schema::create('products', function (Blueprint $table) {
        $table->increments('id');

        $table->string('style_number', 64)->default('');
        $table->string('title')->default('');
        $table->text('overview')->nullable();
        $table->text('description')->nullable();


        $table->timestamps();
    });
}

/**
 * Reverse the migrations.
 *
 * @return void
 */
public function down()
{
    Schema::drop('products');
}

And finally at the very end the file that I am temporarily using to resolve issues, which I will refactor as I write tests for the models which I named 9999_99_99_999999_create_foreign_keys.php. These keys are commented as I pulled them out, but you get the point.

    public function up()
    {
//        Schema::table('product_skus', function ($table) {
//            $table->foreign('product_id')->references('id')->on('products')->onDelete('cascade');
//    });

    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
//        Schema::table('product_skus', function ($table)
//        {
//            $table->dropForeign('product_skus_product_id_foreign');
//        });

Источник

When you try to create a foreign key constraint between two tables, you may encounter the MySQL error 1215 that says Cannot add foreign key constraint.

For example, suppose we have a table called cities with the following data:

# cities table

+----+-----------+
| id | name      |
+----+-----------+
|  1 | London    |
|  2 | York      |
|  3 | Bristol   |
|  4 | Liverpool |
+----+-----------+

Then, suppose we want to create a table named users with a foreign key constraint, referencing the id column from the cities table.

Here’s how we might do it:

CREATE TABLE `users` (
  `user_id` int unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(45) NOT NULL,
  `last_name` varchar(45) NOT NULL,
  `city_id` int DEFAULT NULL,
  PRIMARY KEY (`user_id`),
  FOREIGN KEY (`city_id`) REFERENCES cities(id)
);

The response from MySQL may look like this:

ERROR 1215 (HY000): Cannot add foreign key constraint

Unfortunately, there are many issues that could cause this error.

This tutorial will list the most common cause for ERROR 1215 and give you suggestions on how to fix them.

Make sure that you are using the correct syntax

The first thing to do is to make sure that you are using the correct syntax for creating the FOREIGN KEY constraint.

The syntax to add a foreign key on CREATE TABLE statement must follow this pattern:

FOREIGN KEY (`[target_column_name]`) 
  REFERENCES [origin_table_name]([origin_column_name])

You must replace [target_column_name] next to the FOREIGN KEY syntax with the column name in the current table, while [origin_table_name] and [origin_column_name] must refer to the table and column name of an existing table.

Once you have the correct syntax, make sure that there’s no typo in [target_column_name], [origin_table_name], and [origin_column_name] or you may trigger the same error.

Once you are sure you have the correct syntax, let’s check the engine used by your tables next.

Make sure your tables are using InnoDB engine

You need to check whether the existing table and the table you want to create are using InnoDB engine.

This is because the MyISAM engine doesn’t support adding foreign key constraints, so when you try to add a foreign key constraint to the table, it will trigger the ERROR 1215.

To check the engine of your existing table, you need to run the SHOW TABLE STATUS statement like this:

SHOW TABLE STATUS WHERE name = 'cities';

If you’re using the mysql command line client, then add a G next to the table name to organize the output as lists instead of a table.

Here’s an example output from the command line client:

mysql> SHOW TABLE STATUS WHERE name = 'cities'G
*************************** 1. row ***************************
           Name: cities
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 4
 Avg_row_length: 20
    Data_length: 80
Max_data_length: 281474976710655
   Index_length: 2048
      Data_free: 0
 Auto_increment: 5
    Create_time: 2021-11-13 11:32:14
    Update_time: 2021-11-13 11:32:14
     Check_time: NULL
      Collation: utf8mb4_0900_ai_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.01 sec)

As you can see from the highlighted line, the cities table is using the MyISAM engine.

You can change the engine of your MySQL table by using the ALTER TABLE statement as follows:

ALTER TABLE cities ENGINE = InnoDB;

Once you altered the table engine, you can try to add the foreign key constraint to the new table again.

The default engine used for CREATE TABLE statement should be InnoDB, but you can add the engine explicitly as shown below:

CREATE TABLE `users` (
  `user_id` int unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(45) NOT NULL,
  `last_name` varchar(45) NOT NULL,
  `city_id` int DEFAULT NULL,
  PRIMARY KEY (`user_id`),
  FOREIGN KEY (`city_id`) REFERENCES cities(id)
)
ENGINE = InnoDB;

If the error still happens, then it’s time to check the data type of the two columns.

Make sure the two columns are using the same data type

When adding foreign key constraints, the referenced column and the referencing column must both have the same data type.

An important tip here is to look at the full specification of your column using the DESCRIBE statement.

For example,

DESCRIBE cities;

+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int unsigned | NO   | PRI | NULL    | auto_increment |
| name  | tinytext     | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+

As you can see from the output above, the field id has the data type of int unsigned, but the referencing column city_id on the CREATE TABLE statement has the int type:

CREATE TABLE `users` (
  `user_id` int unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(45) NOT NULL,
  `last_name` varchar(45) NOT NULL,
  `city_id` int DEFAULT NULL,
  PRIMARY KEY (`user_id`),
  FOREIGN KEY (`city_id`) REFERENCES cities(id)
)

Keep in mind that the two columns type for the foreign key constraint must exactly match (int signed with int signed, or int unsigned with int unsigned).

You need to fix this issue by either altering the referenced column or the referencing column until they have the same type

Now that you have the same type for the two columns, you can try adding the foreign key again.

Adding ON DELETE / UPDATE SET NULL clause on a NOT NULL column

One more thing that could cause this error is when you add the ON DELETE SET NULL clause to the FOREIGN KEY constraint while the actual column is set to NOT NULL

Take a look at the highlighted lines on the example below:

CREATE TABLE `users` (
  `user_id` int unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(45) NOT NULL,
  `last_name` varchar(45) NOT NULL,
  `city_id` int unsigned NOT NULL,
  PRIMARY KEY (`user_id`),
  FOREIGN KEY (`city_id`) REFERENCES cities(id) 
    ON DELETE SET NULL
)

While the city_id column is specified as NOT NULL, the ON DELETE SET NULL clause on the FOREIGN KEY constraint will cause the same error.

You need to either set the column as DEFAULT NULL:

`city_id` int unsigned DEFAULT NULL

Or you need to remove the ON DELETE SET NULL clause.

The same thing also happens when you add the ON UPDATE SET NULL clause to the FOREIGN KEY constraint.

For VARCHAR columns, make sure you have the same collation for both tables

When you’re adding a foreign key constraint with columns of VARCHAR types, you need to make sure that both tables are using the same collation.

Just like the engine type, you can check the table collation using the SHOW TABLE STATUS statement.

Here’s an example output from my database:

mysql> SHOW TABLE STATUS WHERE name = 'cities'G
*************************** 1. row ***************************
           Name: cities
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 4
 Avg_row_length: 20
    Data_length: 80
Max_data_length: 281474976710655
   Index_length: 2048
      Data_free: 0
 Auto_increment: 5
    Create_time: 2021-11-13 11:32:14
    Update_time: 2021-11-13 11:32:14
     Check_time: NULL
      Collation: utf8mb4_0900_ai_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.01 sec)

Then, you can check the Collation and Charset you need to use in your CREATE TABLE statement by running the SHOW COLLATION statement as follows:

SHOW COLLATION LIKE '[collation_name]';

The result for collation utf8mb4_0900_ai_ci is as follows:

mysql> SHOW COLLATION LIKE 'utf8mb4_0900_ai_ci%'G
*************************** 1. row ***************************
    Collation: utf8mb4_0900_ai_ci
      Charset: utf8mb4
           Id: 255
      Default: Yes
     Compiled: Yes
      Sortlen: 0
Pad_attribute: NO PAD
1 row in set (0.01 sec)

In your CREATE TABLE statement, add the COLLATE and CHARSET options as shown below:

CREATE TABLE table_name(
   -- ...
)
ENGINE = InnoDB
CHARACTER SET utf8mb4
COLLATE utf8mb4_0900_ai_ci;

That should allow you to add foreign key constraints with columns of VARCHAR type.

Conclusion

Through this tutorial, you’ve learned five things that you can check to resolve the MySQL error 1215 Cannot add foreign key constraint.

This error message is not helpful when trying to find the cause, and in recent MySQL versions, the error has been replaced with more descriptive ones.

For example, when you type the wrong table name, you’ll have ERROR 1824 saying Failed to open the referenced table as shown below:

mysql> CREATE TABLE `users` (
    ->   `user_id` int unsigned NOT NULL AUTO_INCREMENT,
    ->   `first_name` varchar(45) NOT NULL,
    ->   `last_name` varchar(45) NOT NULL,
    ->   `city_id` int unsigned DEFAULT NULL,
    ->   PRIMARY KEY (`user_id`),
    ->   FOREIGN KEY (`city_id`) REFERENCES citiess(id)
    -> );
    
ERROR 1824 (HY000): Failed to open the referenced table 'citiess'

The error message above directly points you to the problem with the syntax.

In another example, different column data types will make MySQL throw ERROR 3780 saying the columns are incompatible:

mysql> CREATE TABLE `users` (
    ->   `user_id` int unsigned NOT NULL AUTO_INCREMENT,
    ->   `first_name` varchar(45) NOT NULL,
    ->   `last_name` varchar(45) NOT NULL,
    ->   `city_id` int DEFAULT NULL,
    ->   PRIMARY KEY (`user_id`),
    ->   FOREIGN KEY (`city_id`) REFERENCES cities(id)
    -> );

ERROR 3780 (HY000): Referencing column 'city_id' and referenced column 'id' 
in foreign key constraint 'users_ibfk_1' are incompatible.

Unfortunately, I wasn’t able to pinpoint the exact MySQL version that updates the error messages.

I have updated MySQL to the latest version 8.0.27, so if you have some free time, you might want to upgrade your MySQL version to at least version 8 so that it gives more helpful error messages.

Good luck in resolving the error! 👍

Источник

/
Aug 03, 2015
/
Laravel, Programming, Tutorial, Web

If you’re getting this error message when executing migration on an existing table, you’re probably having the same issue as I was.

[IlluminateDatabaseQueryException]
SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint (SQL: alter table `pages` add constraint pages_category_id_foreign foreign key (`category_id`) references `categories` (`id`))

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

The problem is because my ‘categories’ table was created as MyISAM type, which doesn’t support foreign key.

The solution is surprisingly very simple: Convert the table to InnoDB type.

So before I create the new table ‘pages’ which reference to the existing table ‘categories’ with a foreign key, I inserted a line to convert the table to InnoDB type.

We can do that easily with Laravel’s DB::statement.

Like this:

// Default type MyISAM doesn't support foreign key
// Convert table to InnoDB
if (Schema::hasTable('categories')) {
    DB::statement('ALTER TABLE categories ENGINE = InnoDB');
}

if (! Schema::hasTable('pages')) {
    Schema::create('pages', function(Blueprint $table)
    {
        $table->increments('id');
        $table->timestamps();
        $table->string('title', 255);
        ... omitted ...
        $table->integer('category_id')->nullable()->unsigned();
        $table->foreign('category_id')->references('id')->on('categories');
    });
}

I was stuck at this for almost half a day trying to figure out why the migration keeps giving me error (although the tables were created nevertheless).

Hope this helps.

Источник