Atache Atache - 4 months ago 19
SQL Question

Alter Table add Foreign Key Reference

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

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