更改MySQL表以添加外键约束会导致错误 [英] Altering MySQL table to add foreign key constraint leads to errors

问题描述

为什么在尝试更改带有外键约束的表时出现错误?

Why am I getting errors when trying to alter a table with a foreign key constraint?

我有1个表，HSTORY，用作所有其他特定历史记录表(例如，USER_HISTORY，BROWSER_HISTORY，PICTURE_HISTORY ...)的基础表.我还包括了PICTURE和USER表，因为它们也被调用.

I have 1 table, HSTORY which I use as a base table for all other specific history tables (ie. USER_HISTORY, BROWSER_HISTORY, PICTURE_HISTORY...). I have also included the PICTURE and USER tables as they get called as well.

历史记录表:

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表:(以防有人好奇)

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表:

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

我的意思是我必须先创建FOREIGNID，但是在SO的许多示例中，上述内容应该可以工作.有人知道为什么会这样吗?

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?

推荐答案

感谢 Michael 指出了我的错误.除非该列已存在，否则我实际上无法做出外键.相反，如果我发出这两个命令，则会创建外键约束:

Thanks to Michael for 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);

这篇关于更改MySQL表以添加外键约束会导致错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！