Adding a Foreign Key to an existing Table

Question

I forgot to add a column which is a foreign key. I tried doing the following:

ALTER TABLE GamesTbl
ADD Console varchar(30) NOT NULL,
INDEX (Console), FOREIGN KEY(Console) references ConsolesTbl(ConsoleName) ON DELETE          CASCADE ON UPDATE CASCADE;

Edit:

I tried adding the field first and after that the constraint like so:

ALTER TABLE GamesTbl
ADD Column Console varchar(30);
ADD CONSTRAINT Console FOREIGN KEY (Console)
REFERENCES ConsolesTbl(ConsoleName);

Answer 1

It seems you want to add the missing column, an index, and also add a foreign key constraint. AFAIK there isn't one single command to do all this all in one go. I would do this in smaller steps :)

Also, note that if the Games Table already has data in it, you will not be able to add a NOT NULL column to it without also setting a default.

Here's one way to do this, viz by adding the missing column as NULLable, setting data, and then changing it to NOT NULL:

(I'm also assuming MySql)

ALTER TABLE GamesTbl ADD Console varchar(30) NULL;
ALTER TABLE GamesTbl ADD FOREIGN KEY(Console) references ConsolesTbl(ConsoleName) ON DELETE CASCADE ON UPDATE CASCADE;
CREATE INDEX IX1_GamesConsole ON GamesTbl(Console);

UPDATE GamesTbl SET Console = 'Playstation'; 
ALTER TABLE GamesTbl CHANGE COLUMN Console Console varchar(30) NOT NULL;

SqlFiddle here