add new column to existing Composite Unique Key

Question

I have table named "room" , and it has the following composite unique key

room_acId_levelId_year_name_unique

formed from FOUR columns :

acId
levelId
year
name

And I want add column isLevel to that key without drop and recreate it

The new key should like this:

room_acId_levelId_year_name_isLevel_unique

with the following columns :

acId
levelId
year
name
isLevel

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.

Thanks

Answer 1

Finally I solved the problem with the following steps:

1. Show index keys for my table room

   SHOW CREATE TABLE room;
   

2. 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)

3. Now I can drop my unique key room_acId_levelId_year_name_unique

   ALTER TABLE room DROP INDEX room_acId_levelId_year_name_unique;
   

4. 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;
   

5. 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.