Altering MySQL table to add foreign key constraint leads to errors

mysql

Solution

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

Problem

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?

Original source