如何添加外键（MySQL）

Question

我对SQL非常陌生，我试图执行一个简单的ALTER TABLE来创建一个新列，并将其用作外键引用我的数据库中的另一个简单表格。我已经修改了两个表格为InnoDB。

然而，在执行ALTER TABLE代码时，我遇到了以下错误：

Error   1452    Cannot add or update a child row: 
a foreign key constraint fails (`toys`.<result 2 when 
explaining filename '#sql-6d4_6'>, CONSTRAINT 
`#sql-6d4_6_ibfk_1` FOREIGN KEY (`toy_id`) REFERENCES `toys` (`toy_id`))    

下面是两个表的描述：

表1：

FIELD       TYPE     NULL   KEY     EXTRA
toy_id      int(11)  NO     PRI     auto_increment
toy varchar(50) YES         

表2：

FIELD       TYPE        NULL   KEY     EXTRA
boy_id      int(11)     NO     PRI      auto_increment
boy         varchar(50) YES 

这是我尝试执行的ALTER查询：

    ALTER TABLE boys
    ADD COLUMN toy_id INT NOT NULL,
    ADD CONSTRAINT toys_toy_id_fk
    FOREIGN KEY(toy_id)
    REFERENCES toys(toy_id);

我已经四处查找试图弄清楚它，但没有成功。谢谢您的帮助，也请善待这位新手 :)

编辑：

以下是两个表的 SHOW CREATE TABLE：

表1：

    CREATE TABLE `toys` (
      `toy_id` int(11) NOT NULL AUTO_INCREMENT,
      `toy` varchar(50) DEFAULT NULL,
      PRIMARY KEY (`toy_id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8

表格2：

    CREATE TABLE `boys` (
      `boy_id` int(11) NOT NULL AUTO_INCREMENT,
      `boy` varchar(50) DEFAULT NULL,
      PRIMARY KEY (`boy_id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8

- KPM

---

移除最后一个 ADD: ... ADD CONSTRAINT toys_toy_id_fk FOREIGN KEY(toy_id) ... - ypercubeᵀᴹ

我尝试过删除ADD CONSTRAINT并保留ADD FOREIGN KEY，以及保留ADD CONSTRAINT并在FOREIGN KEY之前删除“ADD”，但是收到了相同的错误。 - KPM

你能发布 SHOW CREATE TABLE toys 和 SHOW CREATE TABLE boys 的输出吗？ - ypercubeᵀᴹ

当然可以 - 我刚刚更新了帖子，包括每个表的 SHOW CREATE TABLE。 - KPM

Answer 1

如果表中有多于零行数据，而且想要添加的非空列受到了父表格中规定的取值限制，但是这个新列没有默认值且只有空值，那么就不能直接添加。

解决方法是分阶段进行：首先添加列，但不声明为非空列，也不要声明外键。

ALTER TABLE boys
 ADD COLUMN toy_id INT;

然后使用与您的玩具表中某些值匹配的有效数据填充它。

UPDATE boys SET toy_id = ...;

然后将该列更改为 NOT NULL，并创建约束：

ALTER TABLE boys MODIFY COLUMN toy_id INT NOT NULL,
 ADD CONSTRAINT toys_toy_id_fk
 FOREIGN KEY(toy_id)
 REFERENCES toys(toy_id);

- Bill Karwin

---

哎呀，我应该注意到 auto_increment = 6。 - ypercubeᵀᴹ