MySQL - 唯一外键
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5312083/
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 - Unique foreign key
提问by Cleankod
I have to make one of the foreign keys unique. The problem is, I am getting the following message from the phpMyAdmin:
我必须使其中一个外键唯一。问题是，我从 phpMyAdmin 收到以下消息：
The following indexes appear to be equal and one of them should be removed: consignmentnumber_id_UNIQUE, fk_consignments_consignmentnumbers2
So my question is this: should I be bothered? Is it really important not to have such indexes?
所以我的问题是：我应该被打扰吗？没有这样的索引真的很重要吗？
回答by Jan Zyka
Every column with an key (primary, foreign) needs an index. Same with column being unique. You probably created two indexes (one when creating FK and one on Unique constraint). If this is the case just drop one of those indexes.
每个带有键（主键、外键）的列都需要一个索引。与列唯一相同。您可能创建了两个索引（一个在创建 FK 时，一个在唯一约束上）。如果是这种情况，只需删除这些索引之一。
It is overhead for the DB to maintain two equivalent indexes.
DB 维护两个等效索引是开销。
回答by CookieCoder
mysql > create unique index index_bar_id on foos(bar_id);
mysql > alter table foos add constraint index_bar_id foreign key (bar_id) references bars (id);
Read more at http://sixarm.com/about/mysql-create-indexes-foreign-keys-constraints.html
在http://sixarm.com/about/mysql-create-indexes-foreign-keys-constraints.html阅读更多信息
回答by guzoff
For the future, if you want to make your foreign key unique, you can simply modify your foreign key column like this:
将来，如果您想让您的外键唯一，您可以简单地修改您的外键列，如下所示：
ALTER TABLE your_table
MODIFY COLUMN your_fk_column [INT, VARCHAR etc.][NOT NULL] UNIQUE;
回答by Programmer9000
Just so you know, it seems like you can also have UNIQUE foreign keys:
正如您所知，您似乎还可以拥有 UNIQUE 外键：
CREATE TABLE user(
uid INT NOT NULL AUTO_INCREMENT,
username VARCHAR(16) NOT NULL UNIQUE,
email_id INT NOT NULL UNIQUE,
FOREIGN KEY (email_id) REFERENCES email(uid)
ON DELETE CASCADE
ON UPDATE CASCADE,
PRIMARY KEY (uid));
