MySQL：使用两个外键创建表失败并显示“重复键名”错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16699936/
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
MySQL: Creating table with two foreign keys fails with "Duplicate key name" error
提问by Georgios Bitzes
While trying to create a new table, MySQL is giving me an error I cannot explain.
在尝试创建新表时，MySQL 给了我一个我无法解释的错误。
CREATE TABLE Products (
id INT NOT NULL,
PRIMARY KEY(id)
);
CREATE TABLE WarehouseMovements (
time DATETIME NOT NULL,
product1 INT NOT NULL,
product2 INT NOT NULL,
FOREIGN KEY WarehouseMovements(product1) REFERENCES Products(id),
FOREIGN KEY WarehouseMovements(product2) REFERENCES Products(id)
);
This fails with ERROR 1061 (42000): Duplicate key name 'WarehouseMovements'. If I remove the foreign key constraints this succeeds, but I want to make sure product1 and product2 are actually pointing to somewhere.
这失败了ERROR 1061 (42000): Duplicate key name 'WarehouseMovements'。如果我删除外键约束，这会成功，但我想确保 product1 和 product2 实际上指向某个地方。
Default engine is InnoDB.
默认引擎是 InnoDB。
What's wrong with the query?
查询有什么问题？
采纳答案by criticalfix
Try making the FK names distinct:
尝试使 FK 名称不同：
CREATE TABLE Products (
id INT NOT NULL,
PRIMARY KEY(id)
);
CREATE TABLE WarehouseMovements (
time DATETIME NOT NULL,
product1 INT NOT NULL,
product2 INT NOT NULL,
FOREIGN KEY IX_WarehouseMovements_product1(product1) REFERENCES Products(id),
FOREIGN KEY IX_WarehouseMovements_product2(product2) REFERENCES Products(id)
);
UPDATE
更新
That's an index or FK name, not a table name. See create-table-foreign-keys documentation:
那是索引或 FK 名称，而不是表名称。请参阅创建表外键文档：
[CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (index_col_name, ...)
REFERENCES tbl_name (index_col_name,...)
Which says
其中说
index_name represents a foreign key ID. If given, this is ignored if an index for the foreign key is defined explicitly. Otherwise, if MySQL creates an index for the foreign key, it uses index_name for the index name.
index_name 表示外键 ID。如果给定，如果显式定义了外键的索引，则忽略此项。否则，如果 MySQL 为外键创建索引，它将使用 index_name 作为索引名称。
回答by PiLHA
you must to add constraint with different names to FK
您必须向 FK 添加具有不同名称的约束
CREATE TABLE Products (
id INT NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE WarehouseMovements (
time DATETIME NOT NULL,
product1 INT NOT NULL,
product2 INT NOT NULL,
CONSTRAINT fk_product_1 FOREIGN KEY (product1) REFERENCES Products (id),
CONSTRAINT fk_product_2 FOREIGN KEY (product2) REFERENCES Products (id)
);
回答by user5384015
ALTER TABLE `cca_orders`
ADD CONSTRAINT `item_id`
FOREIGN KEY (`item_id`)
REFERENCES `joshi_textile_db`.`cca_items_master`(`id`)
ON DELETE RESTRICT
ON UPDATE RESTRICT;
