MySQL 更改表以提供外键约束
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9471348/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Alter table to give foreign key constraint
提问by luckysing_noobster
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.
我有一个表，它有 2 列，我从两个不同的表中复制。我现在想要做的是在下面显示的列名 email 和 id 上给出外键约束。
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
回答by Andriy M
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 ADDbefore every constraint.
您不是在此语句中添加约束，而是添加了约束s：两个 FOREIGN KEY 子句中的每一个都表示一个单独的约束。尽管如此，根据手册，您应该能够根据需要在单个 ALTER TABLE 语句中添加尽可能多的外键约束。您只需要ADD在每个约束之前包含。
Note that constraint names apply individually to the constraints you are adding, and so you might want to specify CONSTRAINT namefor 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.
请注意，约束名称单独应用于您添加的约束，因此如果您希望它具有特定名称，您可能需要为第二个外键指定。与: 它们适用于直接在它们之前的外键。CONSTRAINT nameON UPDATE/ON DELETE
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;
