I have table named "room" , and it has the following composite unique key
Note that I just want to ALTER the key , not to DROP and create it again
. Also two columns acId and levelId are foreign keys.
Finally I solved the problem with the following steps:
Show index keys for my table room
SHOW CREATE TABLE room;
I have to drop those foreign keys acId and levelId
ALTER TABLE room DROP FOREIGN KEY room_acid_foreign; ALTER TABLE room DROP FOREIGN KEY room_levelid_foreign;
(Remember to use the key names you got on query in step one)
Now I can drop my unique key room_acId_levelId_year_name_unique
ALTER TABLE room DROP INDEX room_acId_levelId_year_name_unique;
Add again my two foreign keys again
ALTER TABLE room ADD FOREIGN KEY (sAcID) REFERENCES SAC(id) ON UPDATE CASCADE ON DELETE RESTRICT; ALTER TABLE room ADD FOREIGN KEY (levelId) REFERENCES Level(id) ON UPDATE CASCADE ON DELETE RESTRICT;
Add my new key of five columns
ALTER TABLE room ADD UNIQUE KEY room_acId_levelId_year_name_isLevel_unique (acId, levelId, year, name, isLevel);
I hope this will help someone else.