Alter Table add Foreign Key Reference

Question

I'm doing a tutorial to learn perl/catalyst and it seems to be a little out of date. I'm trying to alter an already existing column, which was previously a primary key (Already dropped the primary key), into a foreign key. I've tried a bunch of different configurations of the syntax and can't seem to pin it down. This is my most recent attempt:

ALTER TABLE book_author (
   MODIFY book_id INTEGER
   ADD CONSTRAINT FOREIGN KEY book_id
   REFERENCES book(id)
   ON DELETE CASCADE
   ON UPDATE CASCADE
);

Any advice is appreciated.

Answer 1

You use parentheses like you are doing in a CREATE TABLE statement, but not in an ALTER TABLE statement.

You are also missing a comma between the MODIFY and the ADD CONSTRAINT lines.

And you are missing parentheses around the column book_id which is the subject of the constraint.

The following works:

ALTER TABLE book_author
   MODIFY book_id INTEGER,
   ADD CONSTRAINT FOREIGN KEY (book_id)
   REFERENCES book(id)
   ON DELETE CASCADE
   ON UPDATE CASCADE;

This syntax is documented on the official MySQL site: http://dev.mysql.com/doc/refman/5.7/en/alter-table.html