Pdoexception sqlstate hy000 general error 1215 cannot add foreign key constraint

I just wanted to add this case as well for VARCHAR foreign key relation. I spent the last week trying to figure this out in MySQL Workbench 8.0 and was finally able to fix the error.

Short Answer:
The character set and collation of the schema, the table, the column, the referencing table, the referencing column and any other tables that reference to the parent table have to match.

Long Answer:
I had an ENUM datatype in my table. I changed this to VARCHAR and I can get the values from a reference table so that I don’t have to alter the parent table to add additional options. This foreign-key relationship seemed straightforward but I got 1215 error. arvind’s answer and the following link suggested the use of

SHOW ENGINE INNODB STATUS;

On using this command I got the following verbose description for the error with no additional helpful information

Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB-4.1.12, and such columns in old tables
cannot be referenced by such columns in new tables.
Please refer to http://dev.mysql.com/doc/refman/8.0/en/innodb-foreign-key-constraints.html for correct foreign key definition.

After which I used SET FOREIGN_KEY_CHECKS=0; as suggested by Arvind Bharadwaj and the link here:

This gave the following error message:

Error Code: 1822. Failed to add the foreign key constraint. Missing
index for constraint

At this point, I ‘reverse engineer’-ed the schema and I was able to make the foreign-key relationship in the EER diagram. On ‘forward engineer’-ing, I got the following error:

Error 1452: Cannot add or update a child row: a foreign key constraint
fails

When I ‘forward engineer’-ed the EER diagram to a new schema, the SQL script ran without issues. On comparing the generated SQL from the attempts to forward engineer, I found that the difference was the character set and collation. The parent table, child table and the two columns had utf8mb4 character set and utf8mb4_0900_ai_ci collation, however, another column in the parent table was referenced using CHARACTER SET = utf8 , COLLATE = utf8_bin ; to a different child table.

For the entire schema, I changed the character set and collation for all the tables and all the columns to the following:

CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;

This finally solved my problem with 1215 error.

Side Note:
The collation utf8mb4_general_ci works in MySQL Workbench 5.0 or later. Collation utf8mb4_0900_ai_ci works just for MySQL Workbench 8.0 or higher. I believe one of the reasons I had issues with character set and collation is due to MySQL Workbench upgrade to 8.0 in between. Here is a link that talks more about this collation.

I just wanted to add this case as well for VARCHAR foreign key relation. I spent the last week trying to figure this out in MySQL Workbench 8.0 and was finally able to fix the error.

Short Answer:
The character set and collation of the schema, the table, the column, the referencing table, the referencing column and any other tables that reference to the parent table have to match.

Long Answer:
I had an ENUM datatype in my table. I changed this to VARCHAR and I can get the values from a reference table so that I don’t have to alter the parent table to add additional options. This foreign-key relationship seemed straightforward but I got 1215 error. arvind’s answer and the following link suggested the use of

SHOW ENGINE INNODB STATUS;

On using this command I got the following verbose description for the error with no additional helpful information

Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB-4.1.12, and such columns in old tables
cannot be referenced by such columns in new tables.
Please refer to http://dev.mysql.com/doc/refman/8.0/en/innodb-foreign-key-constraints.html for correct foreign key definition.

After which I used SET FOREIGN_KEY_CHECKS=0; as suggested by Arvind Bharadwaj and the link here:

This gave the following error message:

Error Code: 1822. Failed to add the foreign key constraint. Missing
index for constraint

At this point, I ‘reverse engineer’-ed the schema and I was able to make the foreign-key relationship in the EER diagram. On ‘forward engineer’-ing, I got the following error:

Error 1452: Cannot add or update a child row: a foreign key constraint
fails

When I ‘forward engineer’-ed the EER diagram to a new schema, the SQL script ran without issues. On comparing the generated SQL from the attempts to forward engineer, I found that the difference was the character set and collation. The parent table, child table and the two columns had utf8mb4 character set and utf8mb4_0900_ai_ci collation, however, another column in the parent table was referenced using CHARACTER SET = utf8 , COLLATE = utf8_bin ; to a different child table.

For the entire schema, I changed the character set and collation for all the tables and all the columns to the following:

CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;

This finally solved my problem with 1215 error.

Side Note:
The collation utf8mb4_general_ci works in MySQL Workbench 5.0 or later. Collation utf8mb4_0900_ai_ci works just for MySQL Workbench 8.0 or higher. I believe one of the reasons I had issues with character set and collation is due to MySQL Workbench upgrade to 8.0 in between. Here is a link that talks more about this collation.

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.

MySQL Error Code 1215: “Cannot add foreign key constraint”

Updated 7-05-2019

In this blog, we’ll look at how to resolve MySQL error code 1215: “Cannot add foreign key constraint”.

Our Support customers often come to us with things like “My database deployment fails with error 1215”, “Am trying to create a foreign key and can’t get it working” or “Why am I unable to create a constraint?” To be honest, the error message doesn’t help much. You just get the following line:

ERROR 1215 (HY000): Cannot add foreign key constraint

There’s actually a multitude of reasons this can happen, and in this blog post is a compendium of the most common reasons why you can get MySQL Error Code 1215, how to diagnose your case to find which one is affecting you, and potential solutions for adding the foreign key.

(Note: be careful when applying the proposed solutions, as many involve ALTERing the parent table and that can take a long time blocking the table, depending on your table size, MySQL version and the specific ALTER operation being applied; In many cases using pt-online-schema-change will be likely a good idea).

So, onto the solutions:

The best way to start investigating this error is by getting more information about it from LATEST FOREIGN KEY ERROR section of SHOW ENGINE INNODB STATUS . This will give you a hint regarding the problem, which should help you identify your case in the list below.

1) The table or index the constraint refers to does not exist yet (usual when loading dumps).

How to diagnose: Run SHOW TABLES or SHOW CREATE TABLE for each of the parent tables. If you get error 1146 for any of them, it means tables are being created in the wrong order.
How to fix: Run the missing CREATE TABLE and try again, or temporarily disable foreign-key-checks. This is especially needed during backup restores where circular references might exist. Simply run:

Источник

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

Hi, can someone please help me with this one? I’m new to laravel and when I try to do:

php artisan migrate

i get the error:

IlluminateDatabaseQueryException : SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint (SQL: alter table posts add constraint posts_author_id_foreign foreign key ( author_id ) references users ( id ) on delete restrict)

and this is the post table content:

` increments(‘id’);
$table->integer(‘author_id’)->unsigned();
$table->foreign(‘author_id’)->references(‘id’)->on(‘users’)->onDelete(‘restrict’);
$table->string(‘title’);
$table->string(‘slug’)->unique();
$table->text(‘excerpt’);
$table->text(‘body’);
$table->string(‘image’)->nullable();
$table->timestamps();
>);
>

I have noticed that when I comment this line:

the error is gone, but I need it. So, can someone please tell me what will be the correct syntax or how to fix it?

The text was updated successfully, but these errors were encountered:

I tried everything, using:

but i still get the same error every time. in case, here is the complete error:

IlluminateDatabaseQueryException : SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint (SQL: alter table posts add constraint posts_author_id_foreign foreign key ( author_id ) references users ( id ) on delete restrict)

at /var/www/html/becauseican/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|

Источник

Dealing With MySQL Error Code 1215: »Cannot Add Foreign Key Constraint»

Good old error 1215 is common enough, but MySQL doesn’t give you the reason behind it. Fortunately, there are literally a dozen ways to dig deeper,

Join the DZone community and get the full member experience.

In this blog, we’ll look at how to resolve MySQL error code 1215: “Cannot add foreign key constraint”.

Our Support customers often come to us with things like “My database deployment fails with error 1215”, “Am trying to create a foreign key and can’t get it working,” or “Why am I unable to create a constraint?” To be honest, the error message doesn’t help much. You just get the following line:

ERROR 1215 (HY000): Cannot add foreign key constraint

But MySQL never tells you exactly WHY it failed. There’s actually a multitude of reasons this can happen. This blog post is a compendium of the most common reasons why you can get ERROR 1215, how to diagnose your case to find which one is affecting you and potential solutions for adding the foreign key.

(Note: be careful when applying the proposed solutions, as many involve ALTERing the parent table and that can take a long time blocking the table, depending on your table size, MySQL version and the specific ALTER operation being applied; in many cases, using pt-online-schema-change will be likely a good idea).

So, onto the list:

1) The Table or Index the Constraint Refers to Does Not Exist yet (Usual When Loading Dumps)

How to diagnose: Run SHOW TABLES or SHOW CREATE TABLE for each of the parent tables. If you get error 1146 for any of them, it means tables are being created in wrong order.

How to fix: Run the missing CREATE TABLE and try again, or temporarily disable foreign-key-checks. This is especially needed during backup restores where circular references might exist. Simply run:

Example:

2) The Table or Index in the Constraint References Misuses Quotes

How to diagnose: Inspect each FOREIGN KEY declaration and make sure you either have no quotes around object qualifiers, or that you have quotes around the table and a SEPARATE pair of quotes around the column name.

How to fix: Either don’t quote anything, or quote the table and the column separately.

Example:

3) The Local Key, Foreign Table or Column in the Constraint References Have a Typo

How to diagnose: Run SHOW TABLES and SHOW COLUMNS and compare strings with those in your REFERENCES declaration.

How to fix: Fix the typo once you find it.

Example:

4) The Column the Constraint Refers to Is Not of the Same Type or Width as the Foreign Column

How to diagnose: Use SHOW CREATE TABLE parent to check that the local column and the referenced column both have same data type and width.

How to fix: Edit your DDL statement such that the column definition in the child table matches that of the parent table.

Example:

5) The Foreign Object Is Not a KEY of Any Kind

How to diagnose: Use SHOW CREATE TABLE parent to check that if the REFERENCES part points to a column, it is not indexed in any way.

How to fix: Make the column a KEY , UNIQUE KEY or PRIMARY KEY on the parent.

Example:

6) The Foreign Key Is a Multi-Column PK or UK, Where the Referenced Column Is Not the Leftmost One

How to diagnose: Do a SHOW CREATE TABLE parent to check if the REFERENCES part points to a column that is present in some multi-column index(es), but is not the leftmost one in its definition.

How to fix: Add an index on the parent table where the referenced column is the leftmost (or only) column.

Example:

7) Different Charsets/Collations Among the Two Table/Columns

How to diagnose: Run SHOW CREATE TABLE parent and compare that the child column (and table) CHARACTER SET and COLLATE parts match those of the parent table.

How to fix: Modify the child table DDL so that it matches the character set and collation of the parent table/column (or ALTER the parent table to match the child’s wanted definition.

Example:

The Parent Table Is Not Using InnoDB

How to diagnose: Run SHOW CREATE TABLE parent and verify if ENGINE=INNODB or not.

How to fix: ALTER the parent table to change the engine to InnoDB.

Example:

9) Using Syntax Shorthands to Reference the Foreign Key

How to diagnose: Check if the REFERENCES part only mentions the table name. As explained by ex-colleague Bill Karwin in http://stackoverflow.com/questions/41045234/mysql-error-1215-cannot-add-foreign-key-constraint, MySQL doesn’t support this shortcut (even though this is valid SQL).

How to fix: Edit the child table DDL so that it specifies both the table and the column.

Example:

10) The Parent Table Is Partitioned

How to diagnose: Run SHOW CREATE TABLE parent and find out if it’s partitioned or not.
How to fix: Removing the partitioning (i.e., merging all partitions back into a single table) is the only way to get it working.

Example:

11) Referenced Column Is a Generated Virtual Column (This Is Only Possible With 5.7 and Newer)

How to diagnose: Run SHOW CREATE TABLE parent and verify that the referenced column is not a virtual column.

How to fix: CREATE or ALTER the parent table so that the column will be stored and not generated.

Example:

12) Using SET DEFAULT for a Constraint Action

How to diagnose: Check your child table DDL and see if any of your constraint actions ( ON DELETE , ON UPDATE ) try to use SET DEFAULT

How to fix: Remove or modify actions that use SET DEFAULT from the child table CREATE or ALTER statement.

Example:

I realize many of the solutions are not what you might desire, but these are limitations in MySQL that must be overcome on the application side for the time being. I do hope the list above gets shorter by the time 8.0 is released!

If you know other ways MySQL can fail with ERROR 1215, let us know in the comments!

Published at DZone with permission of Marcos Albe , DZone MVB . See the original article here.

Opinions expressed by DZone contributors are their own.

Источник

Pdoexception sqlstate hy000 general error 1215 cannot add foreign key constraint

If I run the migration: n

public function up()n <n // Create table to store users.n Schema::create(‘users’, function (Blueprint $table) <n $table->increments(‘id’);n $table->string(‘first_name’);n $table->string(‘last_name’);n $table->string(‘username’)->unique();n $table->string(’email’)->unique();n $table->string(‘password’);n $table->integer(‘type’);n $table->integer(‘address_id’)->unsigned();n $table->dateTime(‘date_of_birth’);n $table->integer(‘nationality’)->unsigned;n $table->integer(‘gender’)->unsigned();n $table->rememberToken();n $table->timestamps();n >);nn Schema::table(‘users’, function (Blueprint $table) <n $table->foreign(‘address_id’)->references(‘id’)->on(‘addresses’)->onUpdate(‘cascade’);n >);nn // Create table to store the user address.n Schema::create(‘addresses’, function (Blueprint $table) <n $table->increments(‘id’);n $table->string(‘address’);n $table->string(‘postal_code’);n $table->string(‘city’);n $table->string(‘phone’);n $table->string(‘mobile’)->nullable();n $table->timestamps();n >);n >n n

I get the following errors: n

[Illuminate\Database\QueryException]n SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint (SQL: alter table `users` add constraint `users_address_id_foreign` foreign key (`address_id`) references `addresses` (`id`) onn update cascade)n n

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

First you have to create tables and then foreign keys, so n

public function up()n <n // Create table to store users.n Schema::create(‘users’, function (Blueprint $table) <n $table->increments(‘id’);n $table->string(‘first_name’);n $table->string(‘last_name’);n $table->string(‘username’)->unique();n $table->string(’email’)->unique();n $table->string(‘password’);n $table->integer(‘type’);n $table->integer(‘address_id’)->unsigned();n $table->dateTime(‘date_of_birth’);n $table->integer(‘nationality’)->unsigned;n $table->integer(‘gender’)->unsigned();n $table->rememberToken();n $table->timestamps();n >);nn n // Create table to store the user address.n Schema::create(‘addresses’, function (Blueprint $table) <n $table->increments(‘id’);n $table->string(‘address’);n $table->string(‘postal_code’);n $table->string(‘city’);n $table->string(‘phone’);n $table->string(‘mobile’)->nullable();n $table->timestamps();n >);nn Schema::table(‘users’, function (Blueprint $table) <n $table->foreign(‘address_id’)->references(‘id’)->on(‘addresses’)->onUpdate(‘cascade’);n >);nn >n n

That did the job. Should have known this haha. It couldn’t make a foreign key because the address table simply did not exist (yet). n

First you have to create tables and then foreign keys, so n

public function up()n <n // Create table to store users.n Schema::create(‘users’, function (Blueprint $table) <n $table->increments(‘id’);n $table->string(‘first_name’);n $table->string(‘last_name’);n $table->string(‘username’)->unique();n $table->string(’email’)->unique();n $table->string(‘password’);n $table->integer(‘type’);n $table->integer(‘address_id’)->unsigned();n $table->dateTime(‘date_of_birth’);n $table->integer(‘nationality’)->unsigned;n $table->integer(‘gender’)->unsigned();n $table->rememberToken();n $table->timestamps();n >);nn n // Create table to store the user address.n Schema::create(‘addresses’, function (Blueprint $table) <n $table->increments(‘id’);n $table->string(‘address’);n $table->string(‘postal_code’);n $table->string(‘city’);n $table->string(‘phone’);n $table->string(‘mobile’)->nullable();n $table->timestamps();n >);nn Schema::table(‘users’, function (Blueprint $table) <n $table->foreign(‘address_id’)->references(‘id’)->on(‘addresses’)->onUpdate(‘cascade’);n >);nn >n n

We’ll get into building add-ons, starter kits, and even pop the hood and explore just how all the flat file Statamic Magic (or Statamagic, some might say) works. Let’s do this! «,»path»:»/series/learn-statamic-with-jack»,»strippedBody»:»You’ve probably heard of Statamic by now u2014 that rebellious little Laravel-powered content management system that refuses to use databases. Well, it’s pretty grown up now, and in this series we’re going to learn how Statamic can help you build highly-scalable websites quickly and efficiently, including how to run on a database.We’ll get into building add-ons, starter kits, and even pop the hood and explore just how all the flat file Statamic Magic (or Statamagic, some might say) works. Let’s do this!»,»thumbnail»:»https://ik.imagekit.io/laracasts/series/thumbnails/learn-statamic-with-jack.png»,»large_thumbnail»:»https://laracasts.s3.amazonaws.com/series/thumbnails/social-cards/learn-statamic-with-jack.png»,»svgThumbnail»:»https://ik.imagekit.io/laracasts/series/thumbnails/svg/learn-statamic-with-jack.svg»,»slug»:»learn-statamic-with-jack»,»episodeCount»:22,»difficultyLevel»:»Intermediate»,»customUrl»:null,»version»:»Statamic 3″,»version_notes»:null,»complete»:0,»wallpaper»:»https://laracasts.nyc3.digitaloceanspaces.com/series/wallpapers/Learn-Statamic-With-Jack.zip»,»archived»:0,»runTime»:»2h 58m»,»taxonomy»:<«name»:»Frameworks»,»path»:»https://laracasts.com/browse/frameworks»>,»hasChapters»:true,»isLarabit»:0,»isCreatorSeries»:1,»progress»:<«started»:false,»completionPercentage»:0,»episodesCompleted»:0,»completed»:false,»nextEpisodePosition»:1>,»lastUpdated»:»Dec 13, 2022″>,<«id»:176,»className»:»Laracasts\Series»,»title»:»Eloquent Performance Patterns»,»body»:»

In this series, you’ll learn how to drastically improve the performance of your Laravel applications by pushing more work to the database, all while still using the Eloquent ORM.

Источник

Error Message:

Error Code: 1215. Cannot add foreign key constraint

Example:

Error Code: 1215. Cannot add foreign key constraint

Possible Reason:

Case 1: MySQL storage engine.

MySQL supports several storage engines, comparing features of the different mysql storage engines are given below. Note that only InnoDB storage engine supports foreign key, when you are using different mysql storage engine you may get the error code: 1215 cannot add foreign key constraint.

MySQL STORAGE ENGINE FEATURES

Case 2: Key does not exist in the parent table.

When you are trying to reference a key on the parent table which is not exist, you may likely get the error code: 1215 cannot add foreign key constraint. When you are trying to reference a key on parent table which is not a candidate key (either a primary key or a unique key) you may get the error code: 1215 cannot add foreign key constraint. According to definition a foreign key must reference a candidate key of some table. It does not necessarily to be a primary key. MySQL requires index on corresponding referenced key, so you need a unique key.

Case 3: Foreign key definition.

When the definition of the foreign key is different from the reference key, you may get the error code: 1215 cannot add foreign key constraint. The size and sign of the integer must be the same. The character string columns, the character set and collation must be the same. Otherwise you may get the error code: 1215 cannot add foreign key constraint. The length of the string types need not be the same.

Case 4: Foreign key as a primary key.

When you are using composite primary key or implementing one-to-one relationships you may using foreign key as a primary key in your child table. In that case, definition of foreign key should not define as ON DELETE SET NULL. Since primary key cannot be NULL, defining the referential action in such a way may produce the error code: 1215 cannot add foreign key constraint.

Case 5: Referential action – SET NULL.

When you specify SET NULL action and you defined the columns in the child table as NOT NULL, you may get the error code: 1215 cannot add foreign key constraint.

Solution:

Case 1: Storage Engine.

Only MySQL storage engine InnoDB supports foreign key, make sure you are using InnoDB storage engine. You can use the following command to determine which storage engine your server supports.

To determine the storage engine used in the corresponding table, you can run the following command:

MySQL allows you to define storage engine on table level, you can assign the storage engine by using the following statement:

Example:

To alter the storage engine of an existing table, you can run the following statement:

Case 2: Key does not exist in the parent table.

Make sure your parent table contains at least one key to which you are going to create a reference key.

If the key does not present in the table, you can create a new key by using following statement:

Case 3: Foreign key definition.

The data type must be same for both the foreign key and referenced key. The size and sign of integer types must be the same. For character strings the character set and collation must be the same.

Consider the following example to understand this case:

Note that, in the above example, the data type of the id in student table is TINYINT but the data type of the student_id column in book table which referencing the student table.

Here you need to alter the data type of the student_id column in book table. You can alter the data type of the existing column using following statement:

mysql > ALTER TABLE book MODIFY COLUMN  Id TINY INT NOT NULL ;

 After altering the required fields, the new statement may look as follows:

Case 4: Foreign key as a primary key.

When you are implementing one-to-one relationship or composite primary key you may use foreign key as a primary key in your child table. Definition of foreign key should not define as ON DELETE SET NULL. Since primary key cannot be NULL. The following example will illustrate this case better:

Case 5: Referential action – SET NULL.

Make sure when you specify SET NULL action, define the columns in the child table as NOT NULL.

The following example will explain this case clearly:

You can solve this by altering the foreign key column from not null to null. You can do that by using following statement:

mysql > ALTER TABLE book MODIFY reg_no varchar(255) ;

After modifying the table, the new statement may look similar to as follows:




CREATE TABLE student(
                  id INT NOT NULL, 
                  Reg_no varchar (255),
                  Key (Reg_no),            
                  PRIMARY KEY (id)
)ENGINE=INNODB;
CREATE TABLE book
(
                  book_id INT, 
                  reg_no varchar(255) NULL,       /* allowed NULL*/
                  FOREIGN KEY (reg_no) 
                  REFERENCES student(reg_no)
                  ON DELETE SET NULL              /*Action specified as SET NULL*/
) ENGINE=INNODB;




I hope this post will help you to solve the mysql error code: 1215 cannot add foreign key constraint. If you still couldn’t figure out the issue, get in touch with me through contact me page, I will help you to solve this issue.

Finding out why Foreign key creation fail

When MySQL is unable to create a Foreign Key, it throws out this generic error message:

ERROR 1215 (HY000): Cannot add foreign key constraint

– The most useful error message ever.

Fortunately, MySQL has this useful command that can give the actual reason about why it could not create the Foreign Key.

mysql> SHOW ENGINE INNODB STATUS;

That will print out lots of output but the part we are interested in is under the heading ‘LATEST FOREIGN KEY ERROR’:

------------------------
LATEST FOREIGN KEY ERROR
------------------------
2020-08-29 13:40:56 0x7f3cb452e700 Error in foreign key constraint of table test_database/my_table:
there is no index in referenced table which would contain
the columns as the first columns, or the data types in the
referenced table do not match the ones in table. Constraint:
,
CONSTRAINT idx_name FOREIGN KEY (employee_id) REFERENCES employees (id)
The index in the foreign key in table is idx_name
Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-foreign-key-constraints.html for correct foreign key definition.

This output could give you some clue about the actual reason why MySQL could not create your Foreign Key

Reason #1 – Missing unique index on the referenced table

This is probably the most common reason why MySQL won’t create your Foreign Key constraint. Let’s look at an example with a new database and new tables:

In the all below examples, we’ll use a simple ‘Employee to Department” relationship:

mysql> CREATE DATABASE foreign_key_1;
Query OK, 1 row affected (0.00 sec)

mysql> USE foreign_key_1;
Database changed

mysql> CREATE TABLE employees(
    ->     id int,
    ->     name varchar(20),
    ->     department_id int
    -> );
Query OK, 0 rows affected (0.08 sec)

mysql> CREATE TABLE departments(
    ->     id int,
    ->     name varchar(20)
    -> );
Query OK, 0 rows affected (0.07 sec)

As you may have noticed, we have not created the table with PRIMARY KEY or unique indexes. Now let’s try to create Foreign Key constraint between employees.department_id column and departments.id column:

mysql> ALTER TABLE employees ADD CONSTRAINT fk_department_id FOREIGN KEY idx_employees_department_id (department_id) REFERENCES departments(id);
ERROR 1215 (HY000): Cannot add foreign key constraint

Let’s look at the detailed error:

mysql> SHOW ENGINE INNODB STATUS;

------------------------
LATEST FOREIGN KEY ERROR
------------------------
2020-08-31 09:25:13 0x7fddc805f700 Error in foreign key constraint of table foreign_key_1/#sql-5ed_49b:
FOREIGN KEY idx_employees_department_id (department_id) REFERENCES departments(id):
Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB-4.1.12, and such columns in old tables
cannot be referenced by such columns in new tables.
Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-foreign-key-constraints.html for correct foreign key definition.

This is because we don’t have any unique index on the referenced table i.e. departments. We have two ways of fixing this:

Option 1: Primary Keys

Let’s fix this by adding a primary key departments.id

mysql> ALTER TABLE departments ADD PRIMARY KEY (id);
Query OK, 0 rows affected (0.20 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE employees ADD CONSTRAINT fk_department_id FOREIGN KEY idx_employees_department_id (department_id) REFERENCES departments(id);
Query OK, 0 rows affected (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 0

Option 2: Unique Index

mysql> CREATE UNIQUE INDEX idx_department_id ON departments(id);
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE employees ADD CONSTRAINT fk_department_id FOREIGN KEY idx_employees_department_id (department_id) REFERENCES departments(id);
Query OK, 0 rows affected (0.21 sec)
Records: 0  Duplicates: 0  Warnings: 0

Reason #2 – Different data types on the columns

MySQL requires the columns involved in the foreign key to be of the same data types.

mysql> CREATE DATABASE foreign_key_1;
Query OK, 1 row affected (0.00 sec)

mysql> USE foreign_key_1;
Database changed

mysql> CREATE TABLE employees(
    ->     id int,
    ->     name varchar(20),
    ->     department_id int,
    ->     PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql> CREATE TABLE departments(
    ->     id char(20),
    ->     name varchar(20),
    ->     PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.07 sec)

You may have noticed that employees.department_id is int while departments.id is char(20). Let’s try to create a foreign key now:

mysql> ALTER TABLE employees ADD CONSTRAINT fk_department_id FOREIGN KEY idx_employees_department_id (department_id) REFERENCES departments(id);
ERROR 1215 (HY000): Cannot add foreign key constraint

Let’s fix the type of departments.id and try to create the foreign key again:

mysql> ALTER TABLE departments MODIFY id INT;
Query OK, 0 rows affected (0.18 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE employees ADD CONSTRAINT fk_department_id FOREIGN KEY idx_employees_department_id (department_id) REFERENCES departments(id);
Query OK, 0 rows affected (0.26 sec)
Records: 0  Duplicates: 0  Warnings: 0

It works now!

Reason #3 – Different collation/charset type on the table

This is a surprising reason and hard to find out. Let’s create two tables with different collation (or also called charset):

Let’s start from scratch to explain this scenario:

mysql> CREATE DATABASE foreign_key_1;                                                                                        Query OK, 1 row affected (0.00 sec)

mysql> USE foreign_key_1;                                                                                                    Database changed

mysql> CREATE TABLE employees(
    ->     id int,
    ->     name varchar(20),
    ->     department_id int,
    ->     PRIMARY KEY (id)
    -> ) ENGINE=InnoDB CHARACTER SET=utf8;
Query OK, 0 rows affected (0.06 sec)

mysql> CREATE TABLE departments(
    ->     id int,
    ->     name varchar(20),
    ->     PRIMARY KEY (id)
    -> ) ENGINE=InnoDB CHARACTER SET=latin1;
Query OK, 0 rows affected (0.08 sec)

You may notice that we are using a different character set (utf8 and latin1` for both these tables. Let’s try to create the foreign key:

mysql> ALTER TABLE employees ADD CONSTRAINT fk_department_id FOREIGN KEY idx_employees_department_id (department_id) REFERENCES departments(id);
ERROR 1215 (HY000): Cannot add foreign key constraint

It failed because of different character sets. Let’s fix that.

mysql> SET foreign_key_checks = 0; ALTER TABLE departments CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; SET foreign_key_checks = 1;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.18 sec)
Records: 0  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

mysql> ALTER TABLE employees ADD CONSTRAINT fk_department_id FOREIGN KEY idx_employees_department_id (department_id) REFERENCES departments(id);
Query OK, 0 rows affected (0.20 sec)
Records: 0  Duplicates: 0  Warnings: 0

If you have many tables with a different collation/character set, use this script to generate a list of commands to fix all tables at once:

mysql --database=your_database -B -N -e "SHOW TABLES" | awk '{print "SET foreign_key_checks = 0; ALTER TABLE", $1, "CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; SET foreign_key_checks = 1; "}'

Reason #4 – Different collation types on the columns

This is a rare reason, similar to reason #3 above but at a column level.

Let’s try to reproduce this from scratch:

mysql> CREATE DATABASE foreign_key_1;                                                                                        Query OK, 1 row affected (0.00 sec)

mysql> USE foreign_key_1;                                                                                                    Database changed

mysql> CREATE TABLE employees(
    ->     id int,
    ->     name varchar(20),
    ->     department_id char(26) CHARACTER SET utf8,
    ->     PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.07 sec)

mysql> CREATE TABLE departments(
    ->     id char(26) CHARACTER SET latin1,
    ->     name varchar(20),
    ->     PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.08 sec)

We are using a different character set for employees.department_id and departments.id (utf8 and latin1). Let’s check if the Foreign Key can be created:

mysql> ALTER TABLE employees ADD CONSTRAINT fk_department_id FOREIGN KEY idx_employees_department_id (department_id) REFERENCES departments(id);
ERROR 1215 (HY000): Cannot add foreign key constraint

Nope, as expected. Let’s fix that by changing the character set of departments.id to match with employees.department_id:

mysql> ALTER TABLE departments MODIFY id CHAR(26) CHARACTER SET utf8;
Query OK, 0 rows affected (0.20 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE employees ADD CONSTRAINT fk_department_id FOREIGN KEY idx_employees_department_id (department_id) REFERENCES departments(id);
Query OK, 0 rows affected (0.20 sec)
Records: 0  Duplicates: 0  Warnings: 0

It works now!

Reason #5 -Inconsistent data

This would be the most obvious reason. A foreign key is to ensure that your data remains consistent between the parent and the child table. So when you are creating the foreign key, the existing data is expected to be already consistent.

Let’s setup some inconsistent data to reproduce this problem:

mysql> CREATE DATABASE foreign_key_1;                                                                                        Query OK, 1 row affected (0.00 sec)

mysql> USE foreign_key_1;                                                                                                    Database changed

mysql> CREATE TABLE employees(
    ->     id int,
    ->     name varchar(20),
    ->     department_id int,
    ->     PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql> CREATE TABLE departments(
    ->     id int,
    ->     name varchar(20),
    ->     PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.08 sec)

Let’s insert a department_id in employees table that will not exist in departments.id:

mysql> INSERT INTO employees VALUES (1, 'Amber', 145);
Query OK, 1 row affected (0.01 sec)

Let’s create a foreign key now and see if it works:

mysql> ALTER TABLE employees ADD CONSTRAINT fk_department_id FOREIGN KEY idx_employees_department_id (department_id) REFERENCES departments(id);

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`foreign_key_1`.`#sql-5ed_49b`, CONSTRAINT `fk_department_id` FOREIGN KEY (`department_id`) REFERENCES `departments` (`id`))

This error message is atleast more useful. We can fix this in two ways. Either by adding the missing department in departments table or by deleting all the employees with the missing department. We’ll do the first option now:

mysql> INSERT INTO departments VALUES (145, 'HR');
Query OK, 1 row affected (0.00 sec)

Let’s try to create the Foreign Key again:

mysql> ALTER TABLE employees ADD CONSTRAINT fk_department_id FOREIGN KEY idx_employees_department_id (department_id) REFERENCES departments(id);
Query OK, 1 row affected (0.24 sec)
Records: 1  Duplicates: 0  Warnings: 0

It worked this time.

So we have seen 5 different ways a Foreign Key creation can fail and possible solutions of how we can fix them. If you have encountered a reason not listed above, add them in the comments.

If you are using MySQL 8.x, the error message will be a little different:

SQLSTATE[HY000]: General error: 3780 Referencing column 'column' and referenced column 'id' in foreign key constraint 'idx_column_id' are incompatible.