更改 MySQL 表以添加外键约束会导致错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10369271/
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
Altering MySQL table to add foreign key constraint leads to errors
提问by puk
Question:
题：
Why am I getting errors when trying to alter a table with a foreign key constraint?
为什么在尝试更改具有外键约束的表时会出错？
Details:
细节：
I have 1 table, HSTORYwhich I use as a base table for all other specific history tables (ie. USER_HISTORY, BROWSER_HISTORY, PICTURE_HISTORY...). I have also included the PICTUREand USERtables as they get called as well.
我有1台，HSTORY这是我作为一个基础表中使用的所有其他特定历史记录表（即USER_HISTORY，BROWSER_HISTORY，PICTURE_HISTORY...）。我还包括了PICTURE和USER表，因为它们也被调用了。
HISTORY table:
历史表：
CREATE TABLE IF NOT EXISTS HISTORY
(
ID INT NOT NULL AUTO_INCREMENT,
VIEWERID INT NOT NULL ,
VIEWDATE TIMESTAMP NOT NULL DEFAULT NOW(),
PRIMARY KEY (ID),
FOREIGN KEY (VIEWERID) REFERENCES USER(ID)
)
engine=innodb;
USER table:(in case anyone is curious)
USER 表：（以防有人好奇）
CREATE TABLE IF NOT EXISTS USER
(
ID INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (ID)
)
engine=innodb;
PICTURE table:(in case anyone is curious)
图片表：（以防有人好奇）
CREATE TABLE IF NOT EXISTS PICTURE
(
ID INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (ID)
)
engine=innodb;
PICTURE_HISTORY table:
PICTURE_HISTORY 表：
CREATE TABLE IF NOT EXISTS PICTURE_HISTORY LIKE HISTORY;
ALTER TABLE PICTURE_HISTORY
ADD FOREIGN KEY (FOREIGNID) REFERENCES PICTURE(ID);
However, when I do this, I get:
但是，当我这样做时，我得到：
Key column 'FOREIGNID' doesn't exist in table
I take this to mean that I have to first create FOREIGNID, but in many of the examples on SO, the above should work. Anyone know why this is occurring?
我认为这意味着我必须首先创建FOREIGNID，但在 SO 上的许多示例中，上述内容应该有效。有谁知道为什么会这样？
回答by puk
Thanks to Michaelfor pointing out my mistake. I can't actually make a foreign key unless the column already exists. If instead I issue these two commands, the foreign key constraint is created:
感谢迈克尔指出我的错误。除非该列已经存在，否则我实际上无法创建外键。如果我发出这两个命令，则会创建外键约束：
ALTER TABLE PICTURE_HISTORY
ADD COLUMN FOREIGNID INT NOT NULL;
ALTER TABLE PICTURE_HISTORY
ADD FOREIGN KEY (FOREIGNID) REFERENCES PICTURE(ID);
回答by Heinz
You can combine commands for mysql using a comma, like so:
您可以使用逗号组合 mysql 的命令，如下所示：
ALTER TABLE PICTURE_HISTORY
ADD COLUMN FOREIGNID INT NOT NULL,
ADD FOREIGN KEY (FOREIGNID) REFERENCES PICTURE(ID);
