mysql外键定义不正确,关于sql：mysql外键约束格式错误

逃离我推掉我的手 2023-01-16 09:50 110阅读 0赞

我有两个表，table1是具有列ID的父表，而table2是具有列IDFromTable1的父表(不是实际名称)，当我在IDFromTable1中将FK放在table1中的ID上时 >我收到错误Foreign key constraint is incorrectly formed error。 如果要删除table1记录，我想删除表2记录。 谢谢你的帮助

让我知道是否需要其他信息。 我是mysql新手

您的表使用什么引擎？ table2.IDFromTable1和table1.ID的类型是什么？

另外，检查两个表的字符集是否相同。

两个表引擎都是innoDB。 不知道在哪里可以找到字符集，并且都是字符类型。 该ID是table1中的主键

请提供表1和表2的表定义。 您是怎么得到这个错误的？ 您是否使用工具来创建外键？ 看来这不是MySQL本机错误。

@ user516883-您需要帮助来获取表定义吗？ 在HeidiSQL中，您只需单击"创建代码"选项卡。

...以及表定义呢？ 运行SHOW CREATE TABLE table1，然后运行table2

我在HeidiSQL中遇到了同样的问题。您收到的错误非常神秘。我的问题最终是外键列和引用列的类型或长度不同。

外键列为SMALLINT(5) UNSIGNED，引用列为INT(10) UNSIGNED。一旦将它们设置为完全相同的类型，外键创建就可以完美地工作了。

或者可能是引用的列不是主键

我有点类似的问题-引用的表还不存在。哎呀

Ive完全体验了Jake所做的事情，但是Ive在HeidiSQL上遇到了另一个FK问题(不同类型)。 varchars上的FK必须是相同的排序规则。希望以后能对别人有所帮助！

此外，对于HeidiSQL，您也可能忘记了在引用表中设置主键，该主键应该是外键。

\[哑模式打开\]如果将FK字段设置为在已填充的表上具有NOT NULL，则MySQL将填充0值...并且当您尝试添加约束时，它当然会失败，因为您没有引用记录PK = 0 ...

要添加到@Amalgovinus-它不会告诉您所引用的表是否不存在，它只是对您说"外键约束的格式不正确"。

就我而言，这是因为编码和排序规则不同。

6年后，您的答案仍然有效。

@nawfal-我认为它不一定必须是主键，但必须具有索引。主键自动获得索引。

是的，我的签名/无符号整数类型不匹配。

没有人提到分区。如果您的FK是同时也是分区键的PK，它将产生此错误。

此评论对我有所帮助。在我的情况下，这是因为针对由" varchar(36)DEFAULT NULL"定义的外键使用了不同的编码" varchar(36)COLLATE utf8mb4\_general\_ci DEFAULT NULL"(由休眠生成)。

同样使用HeidiSQL，通过更改FK的名称来解决。他们必须是独一无二的！

对我来说，它在一个表中是ID INT(5)UNSIGNED NOT NULL，被用户ID INT(5)引用。刚刚添加了UNSIGNED NOT NULL，它就像一个魅力:)

使用MyISAM引擎创建父表时，我遇到了同样的问题。我解决了一个愚蠢的错误：

确保列是相同的(相同类型)，并且如果引用列不是primary\_key，请确保它是INDEXED。

甚至发生在我身上的是，没有错误，但是没有添加外键(实际上是1，没有1)，但是在添加简单的KEY referencing\_column(referencing\_column)之后，在两个外键定义之前，它们都成功添加了:)

没有索引的键是我的问题。

我遇到了这个问题，问题是我有一个双列主键，并且不能将主键的第二列用作外键。所以我只为主键的第二列添加了自己的索引，然后它起作用了。

定义外键的语法非常宽容，但是对于任何涉足此方法的人来说，外键必须是"相同类型"的事实甚至适用于排序规则，不仅适用于数据类型，长度和位签名。

不是要在模型中混合排序规则(可以吗？)，但是如果要这样做，请确保您的主键和外键字段在phpmyadmin或Heidi SQL中使用相同的排序规则类型。

希望这可以节省您四个小时的反复试验，而这会花费我很多时间。

谢谢！事实证明，我的在线主机使用ISAM引擎，对于本地开发人员，我使用InnoDB。当我从主机备份表到本地...繁荣时。

MariaDB的最新版本似乎使用utf8\_mb4作为默认字符集(当未在服务器配置中显式设置时)，因此COLLATE utf8mb4\_unicode\_ci是我的(意外的)问题(在开发机上)。

我遇到了同样的问题，但是解决了。

只要确保" table1"中的" ID"列具有唯一索引！

当然，这两个表中" ID"和" IDFromTable1"列的类型，长度必须相同。但是您已经知道了。

你让我今天一整天都感觉很好。

乐意效劳！ ;)

不确定细节，但是我有一个带有此错误的复合键，该键通过为列添加单独的唯一索引而得以解决。

被引用的列必须被索引，它不必是唯一的(尽管那是通常的情况)。

只是为了完成。

如果您具有带有VARCHAR(..)的外键，并且所引用表的字符集与引用该表的表的字符集不同，则可能发生此错误。

例如Latin1表中的VARCHAR(50)与UTF8表中的VARCHAR(50)不同。

甚至utf8\_unicode\_ci和utf8\_general\_ci也会导致错误

mysql错误文本没有太大帮助，在我的情况下，该列具有" not null"约束，因此不允许" on delete set null"

多数民众赞成在那，非常感谢！

我有同样的问题，两列都是INT(11)NOT NULL，但我无法创建外键。

我必须禁用外键检查才能成功运行它：

SET FOREIGN\_KEY\_CHECKS=OFF;

ALTER TABLE ... ADD CONSTRAINT ...

SET FOREIGN\_KEY\_CHECKS=ON;

希望这对某人有帮助。

实际上是它的FOREIGN\_KEY\_CHECKS

这确实帮助我进一步通过，但是我的问题是缺少列中的主索引

如果一切正常，只需在foregin key的末尾添加->unsigned();。

如果不起作用，请检查两个字段的数据类型。它们必须相同。

(最近发送)即使字段名称和数据类型相同但排序规则也不相同，也会导致该问题。

例如

TBL

NAME|DATA

TYPE|

COLLATION

ActivityID|INT|

latin1\_general\_ci

ActivityID|INT|

utf8\_general\_ci

尝试将其更改为

TBL

NAME|DATA

TYPE|

COLLATION

ActivityID|INT|

latin1\_general\_ci

ActivityID|INT|

latin1\_general\_ci

....

这对我有用。

显示此错误的另一个可能原因。我创建表的顺序是错误的。我试图从尚未创建的表中引用键。

尽管其他答案很有帮助，但也想分享我的经验。

当我删除一个表，该表的id已被其他表(带有数据)作为外键引用，并尝试重新创建/导入带有其他列的表时，我遇到了问题。

用于重新创建的查询(在phpMyAdmin中生成)如下所示：

CREATE TABLE \`the\_table\` (

\`id\` int(11) NOT NULL,            /\* No PRIMARY KEY index \*/

\`name\` varchar(255) NOT NULL,

\`name\_fa\` varchar(255) NOT NULL,

\`name\_pa\` varchar(255) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

... /\* SOME DATA DUMP OPERATION \*/

ALTER TABLE \`the\_table\`

ADD PRIMARY KEY (\`id\`), /\* PRIMARY KEY INDEX \*/

ADD UNIQUE KEY \`uk\_acu\_donor\_name\` (\`name\`);

您可能会注意到，PRIMARY KEY索引是在导致问题的创建(和插入数据)之后设置的。

解

解决方案是在表定义查询中为被引用为外键的id添加PRIMARY KEY索引，同时还将其从设置了索引的ALTER TABLE部分中删除：

CREATE TABLE \`the\_table\` (

\`id\` int(11) NOT NULL PRIMARY KEY,            /\* <<== PRIMARY KEY INDEX ON CREATION \*/

\`name\` varchar(255) NOT NULL,

\`name\_fa\` varchar(255) NOT NULL,

\`name\_pa\` varchar(255) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

它为我工作。我担心我需要重新安装软件;)

我在使用Alter表在两个表之间添加外键时遇到问题，而对我有所帮助的事情是确保要添加外键关系的每一列都已建立索引。要在PHP myAdmin中执行此操作：

转到表，然后单击"结构"选项卡。

单击索引选项以索引所需的列，如屏幕截图所示：

![4a7020cdb6b5a0a1321798ee71ad7ec7.png][]

索引完要尝试使用外键引用的两列后，便能够成功使用alter表并创建外键关系。您将看到在下面的屏幕快照中对列进行了索引：

![84b029ef23742902c3e31ee44949eb0b.png][]

注意zip\_code如何显示在两个表中。

我在Symfony 2.8中遇到了同样的问题。

一开始我没有得到它，因为外键的int长度等没有类似的问题。

最后，我必须在项目文件夹中执行以下操作。 (服务器重新启动没有帮助！)

app/console doctrine:cache:clear-metadata

app/console doctrine:cache:clear-query

app/console doctrine:cache:clear-result

尝试运行以下命令：

show create table Parent

//and check if type for both tables are the same, like myISAM or innoDB, etc

//Other aspects to check with this error message: the columns used as foreign

keys must be indexed, they must be of the same type

(if i.e one is of type smallint(5) and the other of type smallint(6),

it won't work), and, if they are integers, they should be unsigned.

//or check for charsets

show variables like"character\_set\_database";

show variables like"collation\_database";

//edited: try something like this

ALTER TABLE table2

ADD CONSTRAINT fk\_IdTable2

FOREIGN KEY (Table1\_Id)

REFERENCES Table1(Table1\_Id)

ON UPDATE CASCADE

ON DELETE CASCADE;

尝试运行SHOW ENGINE INNODB STATUS以获取有关该错误的更多详细信息

@SudhirBastakoti-+1！对我来说就做到了。详细信息很有帮助。能够快速解决问题。

感谢S Doerin：

"只是为了完成。

如果您具有带有VARCHAR(..)的外键，并且所引用表的字符集与引用该表的表的字符集不同，则可能发生此错误。

例如Latin1表中的VARCHAR(50)与UTF8表中的VARCHAR(50)不同。"

我解决了这个问题，更改了表的字符类型。

创建的文件有latin1，正确的是utf8。

添加下一行。

默认字符集= utf8;

您需要检查其所有属性(包括"归类")是否相同

检查表引擎，两个表必须是同一引擎，这对我有很大帮助。

好点子！我正在处理MySQL中的Zen Cart数据库，默认情况下，其表全部在MyISAM引擎中。我使用InnoDB引擎添加了一个表，并尝试将一个外键约束从我的表添加到核心Zen Cart。它因这种晦涩的错误格式错误而失败。您可以使用SHOW TABLE STATUS LIKE table\_name;查看每个表的引擎

我正在使用HeidiSQL，并且为了解决此问题，我不得不在被引用表中创建一个索引，并引用所有列。

![455baa87cccbcaab994b7cb7f0e7bdfb.png][]

在mysql中对我来说也一样：InnoDB需要在外键和引用键上建立索引，以便外键检查可以快速进行，而无需进行表扫描。

我为此失去了几个小时！

一个表中的PK是utf8，另一个表中的utf8\_unicode\_ci！

我有同样的问题。

问题是参考列不是主键。

使它成为主键即可解决问题。

它不需要是PK，也可以是UNIQUE NOT NULL。

实际上...就我而言，只需将其设置为正常的索引类型即可。

我刚才遇到了同样的问题。就我而言，我要做的就是确保我在外键中引用的表必须在当前表之前创建(在代码的前面)。因此，如果您引用的是变量(x \* 5)，则系统应该知道x是什么(x必须在前面的代码行中声明)。这解决了我的问题，希望对您有所帮助。

甚至我也遇到了与mysql和liquibase相同的问题。

所以这就是问题所在：

无论是数据类型还是数据类型的大小，您要引用其他表的列的表都不同。

Error appears in below scenario:

Scenario 1:

Table A has column id, type=bigint

Table B column referenced\_id type varchar(this column gets the value from the id column of Table A.)

Liquibase changeset for table B:

referencedTableName="A" \*\*baseColumnNames="referenced\_id\*\*"

referencedColumnNames="id" baseTableName="B" />

Table A changeSet:

Solution:

correct the type of table B to bigint because the referenced table has type bigint.

Scenrario 2:

The type might be correct but the size might not.

e.g. :

Table B : referenced column type="varchar 50"

Table A : base column type ="varchar 255"

Solution change the size of referenced column to that of base table's column size.

我对带有MariaDB 10.1的Laravel 5.1迁移架构生成器有同样的问题。

问题是设置列时我键入了unigned而不是unsigned(缺少s字母)。

修正错字错误后，对我来说是固定的。

检查您是否以适当的大小写指定了表名(如果表名在数据库中区分大小写)。就我而言，我不得不改变

至

注意customer更改为customer。

或者，您可以使用具有图形界面的DBDesigner4来创建数据库并使用FK链接它们。右键单击您的表，然后选择"复制表SQL创建"以创建代码。

![c928776f2a76cb21833d5cf40ec88509.png][]

[4a7020cdb6b5a0a1321798ee71ad7ec7.png]: /images/20221022/9e3a720e8a8a4a7bb69ffc93f6085cdd.png
[84b029ef23742902c3e31ee44949eb0b.png]: /images/20221022/276ec6a0777f407c801f37b52c6e5277.png
[455baa87cccbcaab994b7cb7f0e7bdfb.png]: /images/20221022/3a5670b3d2ec487896597d4ae68b0e27.png
[c928776f2a76cb21833d5cf40ec88509.png]: /images/20221022/5f38cd5f793f42e2b3cb98743e49ad57.png