Altering MySQL table to add foreign key constraint leads to errors

Question

Question:

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

Details:

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.

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)

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:

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?

I'd like to see an example where that should work... It won't work unless `FOREIGNID` already exists. You cannot create a column simply by creating a constraint that uses it. — Michael Berkowski, Apr 29 '12 at 02:43
@Michael would I have to issue two alter statements (one to create `FOREGINID` and another to make it a FOREIGN KEY` or can I do it in one go? — puk, Apr 29 '12 at 02:45
Not actually sure - I've never tried it so I don't know if it will complain if you attempt one statement. Just give it a try: `ALTER TABLE PICTURE_HISTORY ADD FOREIGNID INT NOT NULL ADD CONSTRAINT fk_pictureid FOREIGN KEY (FOREIGNID) REFERENCES PICTURE (ID);` — Michael Berkowski, Apr 29 '12 at 02:48

score 26 · Accepted Answer · edited May 23 '17 at 12:25

26

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);

edited May 23 '17 at 12:25

Community

1
1

answered Apr 29 '12 at 02:49

puk

16,318
29
119
199

score 8 · Answer 2 · answered May 20 '13 at 23:33

8

You can combine commands for mysql using a comma, like so:

ALTER TABLE PICTURE_HISTORY
ADD COLUMN FOREIGNID INT NOT NULL,
ADD FOREIGN KEY (FOREIGNID) REFERENCES PICTURE(ID);

answered May 20 '13 at 23:33

Heinz

471
4
4

although not technically an answer to my original question, thanks for the tip nonetheless – puk May 22 '13 at 02:46
alter table rooms add column productid int not null, add foreign key(productid) references stock(productid) on delete cascade); this didn't work. – VijayaRagavan Jan 18 '14 at 10:55

Altering MySQL table to add foreign key constraint leads to errors

Question:

Details:

2 Answers2

Linked