如何添加外键 (MySQL)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15349382/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to add Foreign Key (MySQL)
提问by KPM
I'm extremely new to SQL and I'm trying to perform a simple ALTER TABLE to create a new column and use it as a foreign key to reference another simple table in my database. I've altered both tables to be InnoDB
我对 SQL 非常陌生，我正在尝试执行一个简单的 ALTER TABLE 来创建一个新列，并将其用作外键来引用我的数据库中的另一个简单表。我已将两个表都更改为 InnoDB
However, when executing the ALTER TABLE code, I get the following error:
但是，在执行 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`))
Below are the DESC of both tables:
以下是两个表的 DESC：
Table 1:
表格1：
FIELD TYPE NULL KEY EXTRA
toy_id int(11) NO PRI auto_increment
toy varchar(50) YES
Table 2:
表 2：
FIELD TYPE NULL KEY EXTRA
boy_id int(11) NO PRI auto_increment
boy varchar(50) YES
And this is the ALTER query I was trying to perform:
这是我试图执行的 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);
I've looked all over trying to figure it out, but with no luck. Thanks in advance, and please be kind to this newbie :)
我四处寻找试图弄清楚，但没有运气。提前致谢，请善待这个新手:)
EDIT:
编辑：
Here are the SHOW CREATE TABLE for both tables:
以下是两个表的 SHOW CREATE TABLE：
TABLE 1:
表格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
TABLE 2:
表 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
回答by Bill Karwin
You can't add a NOT NULL column to a table that has more than zero rows, when the column is constrained to values that match those in the parent table, and yet has only NULL values because it's a new, unpopulated column with no DEFAULT.
当列被约束为与父表中的值匹配的值，但只有 NULL 值时，您不能向具有多于零行的表添加 NOT NULL 列，因为它是一个新的、未填充的列，没有 DEFAULT .
The workaround is to do it in stages: add the column, but don't declare it NOT NULL, and don't declare the foreign key yet.
解决方法是分阶段进行：添加列，但不要将其声明为 NOT NULL，也不要声明外键。
ALTER TABLE boys
ADD COLUMN toy_id INT;
Then populate it with valid data that matches some value(s) in your toys table.
然后用与玩具表中某些值匹配的有效数据填充它。
UPDATE boys SET toy_id = ...;
Then alter the column to be NOT NULL, and create the constraint:
然后将该列更改为 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);
