ikuchris ikuchris - 2 months ago 23
MySQL Question

add new column to existing Composite Unique Key

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

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.