MySQL - Unique foreign key

Question

I have to make one of the foreign keys unique. The problem is, I am getting the following message from the 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?

score 18 · Accepted Answer · answered Mar 15 '11 at 13:05

18

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.

It is overhead for the DB to maintain two equivalent indexes.

answered Mar 15 '11 at 13:05

Jan Zyka

17,460
16
70
118

You can add that creating a FOREIGN KEY in MySQL will actually create an INDEX on the referencing and referenced columns unless one already exists. – Romain Mar 15 '11 at 13:08
So you are saying that I can delete UNIQUE index and column will stay UNIQUE? I added this index by checking "Unique" in MySQL Workbench. – Cleankod Mar 15 '11 at 16:23
6

Currently I guess you have there 2 indexes of types INDEX and UNIQUE. If this is the case you should drop the INDEX one. The UNIQUE is required for being unique and is sufficient for FK. – Jan Zyka Mar 15 '11 at 17:44
See http://dev.mysql.com/doc/refman/5.0/en/create-index.html, UNIQUE is special type of INDEX and INDEX is general index. – Jan Zyka Mar 15 '11 at 17:45
+1 for the reference to the docs. But does it cause problems when both the indexes are the same ? I'm trying to update a record which has two indexes (both on foreign key) and my logic fails some times. Can the index key be slowing down this operation ? – Gyan Aug 04 '12 at 12:28
Not really sure what you mean. It does no harm, it should still act correctly. Updates operations might a bit slower though since DB needs to maintain two indexes rather than one. – Jan Zyka Aug 08 '12 at 07:39

score 7 · Answer 2 · edited Dec 18 '13 at 08:21

7

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

edited Dec 18 '13 at 08:21

zessx

68,042
28
135
158

answered Dec 17 '13 at 16:08

CookieCoder

351
2
6

guzoff · Answer 3 · 2017-06-29T22:22:24.893

5

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;

edited Jun 29 '17 at 22:22

answered Mar 08 '17 at 07:30

guzoff

343
3
9

score 1 · Answer 4 · answered Feb 19 '16 at 05:46

Just so you know, it seems like you can also have UNIQUE foreign keys:

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));

MySQL - Unique foreign key

4 Answers4

Linked