修改表以给出外键约束 [英] Alter table to give foreign key constraint

问题描述

我有一个表，其中有2列是我从两个不同的表中复制的.我现在想做的是对下面显示的列名email和id给出外键约束.

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;

我收到以下错误:

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

推荐答案

您未在此语句中添加约束，而是在添加 constraint s :两个FOREIGN KEY子句意味着一个单独的约束.不过，根据手册，您应该可以添加尽可能多的外部必要时，在单个ALTER TABLE语句中提供关键约束.您只需要在每个约束之前包括ADD.

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.

请注意，约束名称分别适用于您要添加的约束，因此，如果希望第二个外键具有特定名称，则可能需要为第二个外键指定CONSTRAINT name.与ON UPDATE/ON DELETE相同:它们适用于直接位于它们之前的外键.

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;

这篇关于修改表以给出外键约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！