无法在MySQL中添加外键？

Question

我使用MySQL Workbench在一张表中添加了一个外键，但是出现了一些奇怪的错误，下面是SQL语句：

ALTER TABLE `tansung`.`Declaration` ADD COLUMN `goodsId` INT(11) NOT NULL  AFTER `declarationId` , 
    ADD CONSTRAINT `goodsId`
        FOREIGN KEY (`goodsId` )
        REFERENCES `tansung`.`Goods` (`goodsId` )
        ON DELETE NO ACTION
        ON UPDATE NO ACTION
    , ADD INDEX `goodsId` (`goodsId` ASC) ;

当我点击应用时，会出现惊喜！

ERROR 1005: Can't create table 'tansung.#sql-1b10_1' (errno: 150)

SQL Statement:

ALTER TABLE `tansung`.`Declaration` ADD COLUMN `goodsId` INT(11) NOT NULL  AFTER `declarationId` , 
    ADD CONSTRAINT `goodsId`
        FOREIGN KEY (`goodsId` )
        REFERENCES `tansung`.`Goods` (`goodsId` )
        ON DELETE NO ACTION
        ON UPDATE NO ACTION
    , ADD INDEX `goodsId` (`goodsId` ASC)


ERROR: Error when running failback script. Details follow.


ERROR 1050: Table 'Declaration' already exists

SQL Statement:

CREATE TABLE `Declaration` (
    `declarationId` int(11) NOT NULL,
    PRIMARY KEY (`declarationId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

我找不出逻辑上的错误，甚至无法理解这个错误，请帮帮我。

- firefly

---

错误似乎很明显，您正在尝试创建一个名为“Declaration”的表，但该表已经存在... - Brian Driscoll

请确保您不要在同一个窗口中运行所有的SQL代码。看起来您正在运行ALTER TABLE代码，紧接着是CREATE TABLE代码...请确保只运行ALTER TABLE代码以避免该错误。 - acconrad

@Brian Driscoll 真正的错误出现在代码块的顶部 ERROR 1005: Can't create table 'tansung.#sql-1b10_1' (errno: 150)。 - Michael Berkowski

我只执行了一次“alter”操作，没有执行“creat”操作（也许是Workbench添加的？我有点糊涂了）。 - firefly

我有同样的问题，在直接在服务器上测试后，我只能向您保证这不是工作台的问题。 - DerShodan

请给@abhijitcaps打上勾号...这是正确的答案。 - tread

Answer 1

数据库中所有外键名称必须唯一。如果您已经在另一个表上有一个名为“goodsId”的外键，您将收到此错误。

如果相关列的类型（例如INT）和约束（UNIQUE等）不完全相同，则会收到该错误。

- user288926

Answer 2

这可能是由于多种原因导致的。以下是一些常见原因。您也可以称之为语法错误，正是由于这些错误才会出现。

1. 如果FK（外键）表引擎使用MyISAM而PK（主键）表引擎使用InnoDB，则会发生此问题。 MyISAM不支持外键约束。因此，您可能需要将链接表转换为InnoDB。

2. 整个数据库中的所有外键名称必须唯一。如果您已经具有相同名称的另一个表上的外键约束，您将收到此错误。

3. 如果相关列没有完全相同的数据类型（例如INT）和约束（UNIQUE等），则会收到该错误。

- abhijitcaps

---

谢谢，我不知道数据库中所有外键名称都必须唯一。 - vinay

欢迎，@Vinay。很高兴我的答案对你有所帮助。 - abhijitcaps

不匹配的表引擎是我的罪魁祸首。谢谢。 - Doo Dah

谢谢！这正是我想要的。 - David J Eddy

Answer 3

当链接表使用 MyISAM 存储时（在您的情况下为 Goods），并且您要添加索引的表使用 InnoDB 存储（在您的情况下为 Declarations）时，会出现此错误。

您可以通过数据库目录中的文件进行判断。MyISAM 表将具有以下文件：

table_name.frm
table_name.MYD
table_name.MYI

InnoDB表只会有以下内容：

table_name.frm

基础研究后，我建议将您的Goods表转换为InnoDB，因为MyISAM不支持外键约束。

ALTER TABLE Goods ENGINE=INNODB;

在进行此更改后，我的ADD INDEX操作成功完成。

- Neek

Answer 4

我发现在phpMyAdmin中尝试这样做时，表名中有连字符的表只允许一个外键，然后会出现错误。我不知道为什么，但很容易解决，我只需重新制作即可。

 CREATE TABLE `something_new` LIKE `something-old`;
 DROP TABLE `something-old`;

YMMV.

- Matthew Brown aka Lord Matt

Answer 5

像其他人所说的那样，首先确保两列的类型相同并且数据库支持它。之后，确保保存对其他表键的列是有效的。 我曾经遇到过一个问题，我正在将约束添加到一个已存在的带有数据的列中，而该数据与其他表中的任何主键都不匹配，因此创建关系的尝试将失败。修复它涉及更新所有列，以确保我的列数据与我正在尝试创建的约束相匹配。

- Craig S.

Answer 6

我曾经遇到过同样的问题。看起来子表中有一些数据在父表中不存在。你可以进行外连接以查看差异，并为不匹配的行分配有效的ID或删除它们：

DELETE FROM books
WHERE NOT EXISTS (
    SELECT * FROM users
    WHERE books.user_id = users.id
)

- Octavian Vladu

Answer 7

Goods.goodsId和Declarations.goodsId的类型定义必须相同，否则会出现errno: 150。

确保它们都是相同的数据类型，在Declarations表中看起来是goodsId INT(11) NOT NULL。 Goods的CREATE TABLE语句是什么？

- Michael Berkowski

---

可能与我设置“lower_case_table_names=0”有关。 - firefly

因为我需要ORM来生成实体类，所以我想使用大写字母，但是Windows上的MySQL只接受小写表名，所以我设置了lower_case_table_names=0，但是当我添加外键时可能会出现一些问题。 - firefly

Answer 8

错误号150有很多原因。如果您拥有SUPER权限，应尝试使用

SHOW ENGINE INNODB STATUS

这将告诉您导致错误的原因。如果您没有SUPER权限，则需要逐个排除所有可能的原因。您可以在此处找到如何使用SHOW INNODB STATUS以及所有原因的列表：

MySQL外键错误和错误号150

- juacala

Answer 9

一个可能存在的第四个问题（与 abhijitcaps 提出的三个问题不同）是您没有将您正在引用的列设置为主键。

- Linda van der Pal

Answer 10

当我遇到这个错误时，是因为我试图更新一个已经有数据的表格，而这些数据不符合 FK 限制。

- Sean Worthington