Ошибка 1215 phpmyadmin

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.

Источник

Find and fix MySQL issues faster with Percona Monitoring and Management

MySQL Error Code 1215

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:

SET FOREIGN_KEY_CHECKS=0;  

SOURCE /backups/mydump.sql; — restore your backup within THIS session

Example:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

mysql> CREATE TABLE child (

    —>   id INT(10) NOT NULL PRIMARY KEY,

    —>   parent_id INT(10),

    —>   FOREIGN KEY (parent_id) REFERENCES `parent`(`id`)

    —> ) ENGINE INNODB;

ERROR 1215 (HY000): Cannot add foreign key constraint

# We check for the parent table and is not there.

mysql> SHOW TABLES LIKE ‘par%’;

Empty set (0.00 sec)

# We go ahead and create the parent table (we’ll use the same parent table structure for all other example in this blogpost):

mysql> CREATE TABLE parent (

    —>   id INT(10) NOT NULL PRIMARY KEY,

    —>   column_1 INT(10) NOT NULL,

    —>   column_2 INT(10) NOT NULL,

    —>   column_3 INT(10) NOT NULL,

    —>   column_4 CHAR(10) CHARACTER SET utf8 COLLATE utf8_bin,

    —>   KEY column_2_column_3_idx (column_2, column_3),

    —>   KEY column_4_idx (column_4)

    —> ) ENGINE INNODB;

Query OK, 0 rows affected (0.00 sec)

# And now we re-attempt to create the child table

mysql> CREATE TABLE child (

    —>   id INT(10) NOT NULL PRIMARY KEY,drop table child;

    —>   parent_id INT(10),

    —>   FOREIGN KEY (parent_id) REFERENCES `parent`(`id`)

    —> ) ENGINE INNODB;

Query OK, 0 rows affected (0.01 sec)

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:

# wrong; single pair of backticks wraps both table and column

ALTER TABLE child  ADD FOREIGN KEY (parent_id) REFERENCES `parent(id)`;

# correct; one pair for each part

ALTER TABLE child ADD FOREIGN KEY (parent_id) REFERENCES `parent`(`id`);

# also correct; no backticks anywhere

ALTER TABLE child ADD FOREIGN KEY (parent_id) REFERENCES parent(id);

# also correct; backticks on either object (in case it’s a keyword)

ALTER TABLE child ADD FOREIGN KEY (parent_id) REFERENCES parent(`id`);

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:

# wrong; Parent table name is ‘parent’

ALTER TABLE child ADD FOREIGN KEY (parent_id) REFERENCES pariente(id);

# correct

ALTER TABLE child ADD FOREIGN KEY (parent_id) REFERENCES parent(id);

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 the 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:

# wrong; id column in parent is INT(10)

CREATE TABLE child (

  id INT(10) NOT NULL PRIMARY KEY,

  parent_id BIGINT(10) NOT NULL,

  FOREIGN KEY (parent_id) REFERENCES `parent`(`id`)

) ENGINE INNODB;

# correct; id column matches definition of parent table

CREATE TABLE child (

  id INT(10) NOT NULL PRIMARY KEY,

  parent_id INT(10) NOT NULL,

  FOREIGN KEY (parent_id) REFERENCES `parent`(`id`)

) ENGINE INNODB;

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:

# wrong; column_1 is not indexed in our example table

CREATE TABLE child (

  id INT(10) NOT NULL PRIMARY KEY,

  parent_column_1 INT(10),

  FOREIGN KEY (parent_column_1) REFERENCES `parent`(`column_1`)

) ENGINE INNODB;

# correct; we first add an index and then re-attempt creation of child table

ALTER TABLE parent ADD INDEX column_1_idx(column_1);

# and then re-attempt creation of child table

CREATE TABLE child (

  id INT(10) NOT NULL PRIMARY KEY,

  parent_column_1 INT(10),

  FOREIGN KEY (parent_column_1) REFERENCES `parent`(`column_1`)

) ENGINE INNODB;

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:

    # wrong; column_3 only appears as the second part of an index on parent table

CREATE TABLE child (

  id INT(10) NOT NULL PRIMARY KEY,

  parent_column_3 INT(10),

  FOREIGN KEY (parent_column_3) REFERENCES `parent`(`column_3`)

) ENGINE INNODB;

# correct; create a new index for the referenced column

ALTER TABLE parent ADD INDEX column_3_idx (column_3);

# then re-attempt creation of child

CREATE TABLE child (

  id INT(10) NOT NULL PRIMARY KEY,

  parent_column_3 INT(10),

  FOREIGN KEY (parent_column_3) REFERENCES `parent`(`column_3`)

) ENGINE INNODB;

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:

# wrong; the parent table uses utf8/utf8_bin for charset/collation

CREATE TABLE child (

  id INT(10) NOT NULL PRIMARY KEY,

  parent_column_4 CHAR(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci,

  FOREIGN KEY (parent_column_4) REFERENCES `parent`(`column_4`)

) ENGINE INNODB;

# correct; edited DDL so COLLATE matches parent definition

CREATE TABLE child (

  id INT(10) NOT NULL PRIMARY KEY,

  parent_column_4 CHAR(10) CHARACTER SET utf8 COLLATE utf8_bin,

  FOREIGN KEY (parent_column_4) REFERENCES `parent`(`column_4`)

) ENGINE INNODB;

8) 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:

# wrong; the parent table in this example is MyISAM:

CREATE TABLE parent (

  id INT(10) NOT NULL PRIMARY KEY

) ENGINE MyISAM;

# correct: we modify the parent’s engine

ALTER TABLE parent ENGINE=INNODB;

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:

# wrong; only parent table name is specified in REFERENCES

CREATE TABLE child (

  id INT(10) NOT NULL PRIMARY KEY,

  column_2 INT(10) NOT NULL,

  FOREIGN KEY (column_2) REFERENCES parent

) ENGINE INNODB;

# correct; both the table and column are in the REFERENCES definition

CREATE TABLE child (

  id INT(10) NOT NULL PRIMARY KEY,

  column_2 INT(10) NOT NULL,

  FOREIGN KEY (column_2) REFERENCES parent(column_2)

) ENGINE INNODB;

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:

    # wrong: the parent table we see below is using PARTITIONs

CREATE TABLE parent (

  id INT(10) NOT NULL PRIMARY KEY

) ENGINE INNODB

PARTITION BY HASH(id)

PARTITIONS 6;

#correct: ALTER parent table to remove partitioning

ALTER TABLE parent REMOVE PARTITIONING;

11) The 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:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

# wrong; this parent table has a generated virtual column

CREATE TABLE parent (

  id INT(10) NOT NULL PRIMARY KEY,

  column_1 INT(10) NOT NULL,

  column_2 INT(10) NOT NULL,

  column_virt INT(10) AS (column_1 + column_2) NOT NULL,

  KEY column_virt_idx (column_virt)

) ENGINE INNODB;

# correct: make the column STORED so it can be used as a foreign key

ALTER TABLE parent DROP COLUMN column_virt, ADD COLUMN column_virt INT(10) AS (column_1 + column_2) STORED NOT NULL;

# And now the child table can be created pointing to column_virt

CREATE TABLE child (

  id INT(10) NOT NULL PRIMARY KEY,

  parent_virt INT(10) NOT NULL,

  FOREIGN KEY (parent_virt) REFERENCES parent(column_virt)

) ENGINE INNODB;

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:

# wrong; the constraint action uses SET DEFAULT

CREATE TABLE child (

  id INT(10) NOT NULL PRIMARY KEY,

  parent_id INT(10) NOT NULL,

  FOREIGN KEY (parent_id) REFERENCES parent(id) ON UPDATE SET DEFAULT

) ENGINE INNODB;        

# correct; there’s no alternative to SET DEFAULT, removing or picking other is the corrective measure

CREATE TABLE child (

  id INT(10) NOT NULL PRIMARY KEY,

  parent_id INT(10) NOT NULL,

  FOREIGN KEY (parent_id) REFERENCES parent(id)

) ENGINE INNODB;

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!

13) Using SET NULL for a constraint on a column defined as NOT NULL

How to diagnose: Check your child table DDL and see if the constraint column is defined with NOT NULL

How to fix: If the table already exists, then ALTER the table and MODIFY the column to remove the NOT NULL. Otherwise, edit your CREATE TABLE and remove the NOT NULL from the relevant column definition.

Example:

# wrong; the constraint column uses NOT NULL

CREATE TABLE child (

  id INT(10) NOT NULL PRIMARY KEY,

  parent_id INT(10) NOT NULL,

  FOREIGN KEY (parent_id) REFERENCES parent(id) ON UPDATE SET NULL

) ENGINE INNODB;        

# correct; make the parent_id column accept NULLs (i.e. remove the NOT NULL)

CREATE TABLE child (

  id INT(10) NOT NULL PRIMARY KEY,

  parent_id INT(10),

  FOREIGN KEY (parent_id) REFERENCES parent(id) ON UPDATE SET NULL

) ENGINE INNODB;

If you know other ways MySQL Error Code 1215 occurs, let us know in the comments!

More information regarding Foreign Key restrictions can be found here.

Источник

So I’m trying to add Foreign Key constraints to my database as a project requirement and it worked the first time or two on different tables, but I have two tables on which I get an error when trying to add the Foreign Key Constraints.
The error message that I get is:

ERROR 1215 (HY000): Cannot add foreign key constraint

This is the SQL I’m using to create the tables, the two offending tables are Patient and Appointment.

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=1;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

CREATE SCHEMA IF NOT EXISTS `doctorsoffice` DEFAULT CHARACTER SET utf8 ;
USE `doctorsoffice` ;

-- -----------------------------------------------------
-- Table `doctorsoffice`.`doctor`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `doctorsoffice`.`doctor` ;

CREATE  TABLE IF NOT EXISTS `doctorsoffice`.`doctor` (
  `DoctorID` INT(11) NOT NULL AUTO_INCREMENT ,
  `FName` VARCHAR(20) NULL DEFAULT NULL ,
  `LName` VARCHAR(20) NULL DEFAULT NULL ,
  `Gender` VARCHAR(1) NULL DEFAULT NULL ,
  `Specialty` VARCHAR(40) NOT NULL DEFAULT 'General Practitioner' ,
  UNIQUE INDEX `DoctorID` (`DoctorID` ASC) ,
  PRIMARY KEY (`DoctorID`) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `doctorsoffice`.`medicalhistory`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `doctorsoffice`.`medicalhistory` ;

CREATE  TABLE IF NOT EXISTS `doctorsoffice`.`medicalhistory` (
  `MedicalHistoryID` INT(11) NOT NULL AUTO_INCREMENT ,
  `Allergies` TEXT NULL DEFAULT NULL ,
  `Medications` TEXT NULL DEFAULT NULL ,
  `ExistingConditions` TEXT NULL DEFAULT NULL ,
  `Misc` TEXT NULL DEFAULT NULL ,
  UNIQUE INDEX `MedicalHistoryID` (`MedicalHistoryID` ASC) ,
  PRIMARY KEY (`MedicalHistoryID`) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `doctorsoffice`.`Patient`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `doctorsoffice`.`Patient` ;

CREATE  TABLE IF NOT EXISTS `doctorsoffice`.`Patient` (
  `PatientID` INT unsigned NOT NULL AUTO_INCREMENT ,
  `FName` VARCHAR(30) NULL ,
  `LName` VARCHAR(45) NULL ,
  `Gender` CHAR NULL ,
  `DOB` DATE NULL ,
  `SSN` DOUBLE NULL ,
  `MedicalHistory` smallint(5) unsigned NOT NULL,
  `PrimaryPhysician` smallint(5) unsigned NOT NULL,
  PRIMARY KEY (`PatientID`) ,
  UNIQUE INDEX `PatientID_UNIQUE` (`PatientID` ASC) ,
  CONSTRAINT `FK_MedicalHistory`
    FOREIGN KEY (`MEdicalHistory` )
    REFERENCES `doctorsoffice`.`medicalhistory` (`MedicalHistoryID` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `FK_PrimaryPhysician`
    FOREIGN KEY (`PrimaryPhysician` )
    REFERENCES `doctorsoffice`.`doctor` (`DoctorID` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `doctorsoffice`.`Appointment`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `doctorsoffice`.`Appointment` ;

CREATE  TABLE IF NOT EXISTS `doctorsoffice`.`Appointment` (
  `AppointmentID` smallint(5) unsigned NOT NULL AUTO_INCREMENT ,
  `Date` DATE NULL ,
  `Time` TIME NULL ,
  `Patient` smallint(5) unsigned NOT NULL,
  `Doctor` smallint(5) unsigned NOT NULL,
  PRIMARY KEY (`AppointmentID`) ,
  UNIQUE INDEX `AppointmentID_UNIQUE` (`AppointmentID` ASC) ,
  CONSTRAINT `FK_Patient`
    FOREIGN KEY (`Patient` )
    REFERENCES `doctorsoffice`.`Patient` (`PatientID` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `FK_Doctor`
    FOREIGN KEY (`Doctor` )
    REFERENCES `doctorsoffice`.`doctor` (`DoctorID` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `doctorsoffice`.`InsuranceCompany`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `doctorsoffice`.`InsuranceCompany` ;

CREATE  TABLE IF NOT EXISTS `doctorsoffice`.`InsuranceCompany` (
  `InsuranceID` smallint(5) NOT NULL AUTO_INCREMENT ,
  `Name` VARCHAR(50) NULL ,
  `Phone` DOUBLE NULL ,
  PRIMARY KEY (`InsuranceID`) ,
  UNIQUE INDEX `InsuranceID_UNIQUE` (`InsuranceID` ASC) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `doctorsoffice`.`PatientInsurance`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `doctorsoffice`.`PatientInsurance` ;

CREATE  TABLE IF NOT EXISTS `doctorsoffice`.`PatientInsurance` (
  `PolicyHolder` smallint(5) NOT NULL ,
  `InsuranceCompany` smallint(5) NOT NULL ,
  `CoPay` INT NOT NULL DEFAULT 5 ,
  `PolicyNumber` smallint(5) NOT NULL AUTO_INCREMENT ,
  PRIMARY KEY (`PolicyNumber`) ,
  UNIQUE INDEX `PolicyNumber_UNIQUE` (`PolicyNumber` ASC) ,
  CONSTRAINT `FK_PolicyHolder`
    FOREIGN KEY (`PolicyHolder` )
    REFERENCES `doctorsoffice`.`Patient` (`PatientID` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `FK_InsuranceCompany`
    FOREIGN KEY (`InsuranceCompany` )
    REFERENCES `doctorsoffice`.`InsuranceCompany` (`InsuranceID` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

USE `doctorsoffice` ;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

Источник

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! 👍

Источник

Я пытаюсь перенаправить новую схему на мой сервер db, но я не могу понять, почему я получаю эту ошибку. Я попытался найти ответ здесь, но все, что я нашел, сказал, либо установил db-движок в Innodb, либо чтобы убедиться, что ключи, которые я пытаюсь использовать в качестве внешнего ключа, являются первичными ключами в их собственных таблицах, Я сделал это, если не ошибаюсь. Любая другая помощь, которую вы, ребята, могли бы предложить?

Executing SQL script in server

ERROR: Error 1215: Cannot add foreign key constraint

-- -----------------------------------------------------
-- Table `Alternative_Pathways`.`Clients_has_Staff`
-- -----------------------------------------------------

CREATE  TABLE IF NOT EXISTS `Alternative_Pathways`.`Clients_has_Staff` (
  `Clients_Case_Number` INT NOT NULL ,
  `Staff_Emp_ID` INT NOT NULL ,
  PRIMARY KEY (`Clients_Case_Number`, `Staff_Emp_ID`) ,
  INDEX `fk_Clients_has_Staff_Staff1_idx` (`Staff_Emp_ID` ASC) ,
  INDEX `fk_Clients_has_Staff_Clients_idx` (`Clients_Case_Number` ASC) ,
  CONSTRAINT `fk_Clients_has_Staff_Clients`
    FOREIGN KEY (`Clients_Case_Number` )
    REFERENCES `Alternative_Pathways`.`Clients` (`Case_Number` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Clients_has_Staff_Staff1`
    FOREIGN KEY (`Staff_Emp_ID` )
    REFERENCES `Alternative_Pathways`.`Staff` (`Emp_ID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB

Выполнение SQL script завершено: операторы: 7 успешно, 1 неудачно

Вот SQL для родительских таблиц.

CREATE  TABLE IF NOT EXISTS `Alternative_Pathways`.`Clients` (
  `Case_Number` INT NOT NULL ,
  `First_Name` CHAR(10) NULL ,
  `Middle_Name` CHAR(10) NULL ,
  `Last_Name` CHAR(10) NULL ,
  `Address` CHAR(50) NULL ,
  `Phone_Number` INT(10) NULL ,
  PRIMARY KEY (`Case_Number`) )
ENGINE = InnoDB

CREATE  TABLE IF NOT EXISTS `Alternative_Pathways`.`Staff` (
  `Emp_ID` INT NOT NULL ,
  `First_Name` CHAR(10) NULL ,
  `Middle_Name` CHAR(10) NULL ,
  `Last_Name` CHAR(10) NULL ,
  PRIMARY KEY (`Emp_ID`) )
ENGINE = InnoDB
Источник

Понравилась статья? Поделить с друзьями:
  • Ошибка 12142 болид орион
  • Ошибка 1213 ситроен
  • Ошибка 1213 пежо 308
  • Ошибка 1213 ниссан
  • Ошибка 1212 газель