12

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
rene
  • 41,474
  • 78
  • 114
  • 152
luckysing_noobster
  • 1,933
  • 5
  • 24
  • 50

1 Answers1

35

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;
Andriy M
  • 76,112
  • 17
  • 94
  • 154
  • 1
    This looks correct. In my experience, I don't even manually name contraints or indices, I let MySQL handle it behind the scenes. – Mike Purcell Feb 28 '12 at 21:10
  • Yes, naming is optional. In SQL Server, where it's optional as well, I prefer to specify the names, though. – Andriy M Feb 28 '12 at 21:32
  • 2
    I get the error. ERROR 1005 (HY000): Can't create table 'acsuserdatabase_dbo.#sql-984_10e' (errno : 150) – luckysing_noobster Feb 28 '12 at 22:22
  • 1
    @luckysing_noobster: From the [manual](http://dev.mysql.com/doc/refman/5.0/en/innodb-error-codes.html "InnoDB Error Codes (MySQL)"): *“`1005 (ER_CANT_CREATE_TABLE)` Cannot create table. If the error message refers to error 150, table creation failed because a foreign key constraint was not correctly formed.”* – invalid references in one of the would-be foreign key columns? – Andriy M Feb 28 '12 at 22:35
  • 2
    @MikePurcell: I tend to pick a name for a foreign key in such a way that it would be helpful to see what the foreign key references whenever and wherever its name is displayed. For instance, in case of a foreign key violation error, the violated key's name is displayed in the error message. Also, I can tell whether a specific foreign key has been defined on a table simply by looking at the list of its foreign keys. And when I need to drop an existing key, it's usually easy to figure out its name using the established naming rules. – Andriy M Feb 28 '12 at 22:54