Add index on same field as other index drops other index
When I add an index that has the same fields as another existing index, the existing index disappears.
I am trying to rename the indexes that are generated by creating an FK.
MySQL 5.5.24
Here is sample code that fails:
CREATE TABLE `table1` (
`sid` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`sid`),
UNIQUE KEY `uq_table1` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=3823 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE `table2` (
`sid` int(11) NOT NULL AUTO_INCREMENT,
`table1_sid` int(11) NOT NULL,
PRIMARY KEY (`sid`),
UNIQUE KEY `uq1_table1_sid` (`table1_sid`),
CONSTRAINT `fk_table2_to_table1` FOREIGN KEY (`table1_sid`) REFERENCES `table1` (`sid`)
) ENGINE=InnoDB AUTO_INCREMENT=128 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
ALTER TABLE table2 DROP INDEX uq1_table1_sid, DROP FOREIGN KEY fk_table2_to_table1;
ALTER TABLE table2 ADD CONSTRAINT fk_table2_to_table1 FOREIGN KEY (table1_sid)
REFERENCES table1 (sid);
CREATE INDEX `fk_table2_to_table1_idx` ON `table2` (`table1_sid` ASC) ;
ALTER TABLE `table2` DROP INDEX `fk_table2_to_table1`;
Result: The last statement gets:
ERROR 1091 (42000) at line 28: Can't DROP 'fk_table2_to_table1'; check that column/key exists
Subject
Views
Written By
Posted
Add index on same field as other index drops other index
1472
June 20, 2013 10:19AM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.