Geek asks and answers

MySQL Error "Invalid index name ..." (unique foreign key)

I get the error "Invalid index name" f7 "using MySQL, and I narrowed it down to the following:

First I create a 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 led me to think that this is due to a duplicate index (?). I just can't figure out how to fix this. Many thanks.

+5
source share
2 answers

Give it a name, so it does not conflict with the foreign key index

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

The error incorrect index nameoccurs when you try to create a new index with the same name as the existing index.

MySQL, , FOREIGN KEY (f7) REFERENCES testTable2 (id) ON DELETE CASCADE ON UPDATE CASCADE, . f7.

; : ALTER TABLE testTable ADD UNIQUE (f7); - .

, , :

SHOW INDEXES FROM testTable;

, , , , f7. CREATE TABLE, - , :

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

fk_testTable_f7, fk_testTable_f7. , ALTER, , , .

+5

More articles:


All Articles