Alter table to give foreign key constraint

Question

I have a table which has 2 columns which I copied from two different tables.What I want to do now is give a foreign key constraint on both the column names email and id shown below.

ALTER TABLE users_role_map
ADD CONSTRAINT FK_users_role_map
FOREIGN KEY (email) REFERENCES usert(email),
FOREIGN KEY (id) REFERENCES rolet(id)
ON UPDATE CASCADE
ON DELETE CASCADE;

I get the following error:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'FOREI
GN KEY (id) REFERENCES rolet(id)
ON UPDATE CASCADE
ON DELETE CASCADE' at line 4

Answer 1

You are not adding a constraint in this statement, you are adding constraints: each of the two FOREIGN KEY clauses means a separate constraint. Still, according to the manual, you should be able to add as many foreign key constraints in a single ALTER TABLE statement as necessary. You just need to include ADD before every constraint.

Note that constraint names apply individually to the constraints you are adding, and so you might want to specify CONSTRAINT name for the second foreign key if you want it to have a specific name. Same with ON UPDATE/ON DELETE: they apply to the foreign key that is directly preceding them.

So, the corrected statement might look like this:

ALTER TABLE users_role_map

ADD CONSTRAINT FK_users_role_map1
FOREIGN KEY (email) REFERENCES usert(email)
ON UPDATE CASCADE
ON DELETE CASCADE,

ADD CONSTRAINT FK_users_role_map2
FOREIGN KEY (id) REFERENCES rolet(id)
ON UPDATE CASCADE
ON DELETE CASCADE;