MySQL “Incorrect index name…” error (unique foreign key)

Question

I keep getting an error "Incorrect index name 'f7'" using MySQL and I've narrowed it down to the following:

First I create the table,

CREATE TABLE testTable (
    id INTEGER PRIMARY KEY AUTO_INCREMENT,
    f7 INTEGER NOT NULL,
    FOREIGN KEY (f7) REFERENCES testTable2 (id) ON DELETE CASCADE ON UPDATE CASCADE,
) ENGINE=InnoDB;

And then elsewhere,

ALTER TABLE testTable ADD UNIQUE f7;

This has led me to believe that this has to do with a duplicate index (?) I just can't figure out how to fix it. Many thanks.

Answer 1

Give it a name, so it doesn't conflict with the foreign Key index

ALTER TABLE `testtable`  ADD UNIQUE INDEX `foo` (`f7`);

Answer 2

An incorrect index name error is given when you're attempting to create a new index with the same name as an existing index.

In MySQL, when you create a foreign key, as you're doing with FOREIGN KEY (f7) REFERENCES testTable2 (id) ON DELETE CASCADE ON UPDATE CASCADE, an index is auto-created as well. In this case, the name is defaulted to f7.

The foreign key is created as a non-unique index; your second command: ALTER TABLE testTable ADD UNIQUE (f7); will make this index unique - not add a second one.

To verify what indexes already exist on the table, you can use the following:

SHOW INDEXES FROM testTable;

If you're receiving this error, there is likely additional code elsewhere that is attempting to create an index named f7. You can attempt to find it, or change your CREATE TABLE syntax to name the key something different so that it doesn't cause conflicts:

FOREIGN KEY fk_testTable_f7 (f7) REFERENCES testTable2 (id) ON DELETE CASCADE ON UPDATE CASCADE

In this example, I used fk_testTable_f7 and you should now have a non-unique index on the table named fk_testTable_f7. To make it unique, you can use your existing ALTER command as you want the column to be unique - not the foreign key itself.