SQL Error (1215): Cannot add foreign key constrain

问题:

CREATE TABLE `profilePic` (
    `ClientID` VARCHAR(255) NOT NULL,
    PRIMARY KEY (`ClientID`),
    CONSTRAINT `FK__user_details` FOREIGN KEY (`ClientID`) REFERENCES `user_details` (`ClientID`) ON UPDATE CASCADE ON DELETE CASCADE
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
;

I am trying to add table with foreign key and I got this error, why that happend ?

1. trying doing new table.
2. i am trying to put same details on user_details->ClientID and profilePic->ClientID 3.i have already one table call`d userdb and in this table i have ClientID and its foreign key and its work.

回答1:

The below will fail because the collation is different. Why do I show this? Because the OP didn't.

Note I shrunk the size due to error 1071 on sizing for varchar 255 with that collation and then auto chosen charset.

The point being, if collation is different, it won't work.

CREATE TABLE `user_details` (
    `ClientID` VARCHAR(100) NOT NULL,
    PRIMARY KEY (`ClientID`)
)ENGINE=InnoDB;

CREATE TABLE `profilePic` (
    `ClientID` VARCHAR(100) NOT NULL,
    PRIMARY KEY (`ClientID`),
    CONSTRAINT `FK__user_details` FOREIGN KEY (`ClientID`) REFERENCES `user_details` (`ClientID`) ON UPDATE CASCADE ON DELETE CASCADE
)COLLATE='utf8mb4_unicode_ci' ENGINE=InnoDB;

The above failure is at the table level. A trickier one causing a 1215 error due to column-level collation mismatches can be seen in This answer.

Pulling the discussion up to more general cases ...

whether you are trying to establish a Foreign Key constraint on table creation or with ALTER TABLE

| ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,...) reference_definition

such as

ALTER TABLE `facility` ADD CONSTRAINT `fkZipcode` 
     FOREIGN KEY (`zipcode`) REFERENCES `allzips`(`zipcode`);

the following will apply.

From the MySQL manual page entitled Using FOREIGN KEY Constraints:

Corresponding columns in the foreign key and the referenced key must have similar data types. The size and sign of integer types must be the same. The length of string types need not be the same. For nonbinary (character) string columns, the character set and collation must be the same.

In addition, the referenced (parent) table must have a left-most key available for fast lookup (verification). That parent key does not need to be PRIMARY or even UNIQUE. This concept is described in the 2nd chunk below. The first chunk alludes to a Helper index that will be created if necessary in the referencing (child) table if so necessary.

MySQL requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Such an index is created on the referencing table automatically if it does not exist. This index might be silently dropped later, if you create another index that can be used to enforce the foreign key constraint. index_name, if given, is used as described previously.

InnoDB permits a foreign key to reference any column or group of columns. However, in the referenced table, there must be an index where the referenced columns are listed as the first columns in the same order.

回答2:

When trying to create a foreign key via HeidiSQL, you get a warning as soon as the selected column data types don't match. I added this warning to HeidiSQL's table designer due to the non-intuitive message from the server ("Cannot add foreign key constraint")

The selected foreign column do not match the source columns data type and unsigned flag. This will give you an error message when trying to save this change. Please compare yourself: