SQL错误（1215）：无法添加外键约束 [英] SQL Error (1215): Cannot add foreign key constraint

问题描述

  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 
; 
  

我试图用外键添加表，我得到这个错误，为什么happend？ user_details-> ClientID 和 profilePic-> ClientID
3.i已经有一个table call`d userdb，我有ClientID及其外键及其工作。

解决方案

以下操作将失败，因为排序规则不同。为什么要显示这个？因为OP没有。

注意，我缩小了大小，由于错误1071大小的varchar 255与排序规则，然后自动选择字符集。

$

  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; 
  

上述失败是在表级别。由于列级归类不匹配导致1215错误的棘手问题可以在此答案中看到。

将讨论引向更一般的情况...

无论您是尝试在表创建时建立外键约束， a href =http://dev.mysql.com/doc/refman/5.7/en/alter-table.html =nofollow> ALTER TABLE

| ADD [CONSTRAINT [symbol]]
FOREIGN KEY [index_name]（index_col_name，...）
reference_definition

如

  ALTER TABLE`facility` ADD CONSTRAINT`fkZipcode`
 FOREIGN KEY（`zipcode`）REFERENCES` `zipz``（`zipcode`）; 
  

以下将适用。

标题为的MySQL手册页使用FOREIGN KEY约束 a>：

外键和引用键的对应列必须
具有相似的数据类型。整型类型的大小和符号必须是
相同。字符串类型的长度不必相同。对于
非二进制（字符）字符串列，字符集和整理
必须相同。

< blockquote>

此外，引用的（父）表必须有一个最左键可用于快速查找（验证）。该父键不需要 PRIMARY 或甚至 UNIQUE 。这个概念在下面的第2块中描述。如果必要，第一个块暗指将在引用（子）表中创建的辅助索引。

MySQL需要对外键和引用的键进行索引，以便
外键检查可以更快，而不需要表扫描。在
引用表中，必须有一个索引，其中外键
列被列为同一顺序中的第一列。如果不存在
，则自动地在引用表上创建这样的
索引。如果您创建
另一个可用于强制外键约束的索引，则此索引可能会在以后静默删除。
index_name，如果给定，如前所述使用。

InnoDB允许外键引用任何列或组的
列。但是，在引用的表中，必须有一个索引
，其中被引用的列被列为
相同顺序中的第一列。

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.

解决方案

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.

这篇关于SQL错误（1215）：无法添加外键约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！