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
ON DELETE CASCADE
ON UPDATE CASCADE
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;
I suggest you learn how to read syntax documentation: http://dev.mysql.com/doc/refman/5.7/en/alter-table.html