Modify UNIQUE constraint

[HelMaedb]

I have an InnoDb table with a named UNIQUE CONSTRAINT. The constraint has been created with an ALTER TABLE script:

ALTER TABLE MyTable 
ADD CONSTRAINT UN_MyTable UNIQUE (field1);

Now I need to add field2 to the constraint with an ALTER script to update old databases (I can't drop and re-create the table). I've tried with

ALTER TABLE MyTable
ALTER CONSTRAINT UN_MyTable UNIQUE(field1, field2);

but it don't work.

Anyone can help?

 

[swampBoogie]

drop and recreate the constraint.

 

[HelMaedb]

I've tryed, but

ALTER TABLE MyTable
DROP CONSTRAINT UN_MyTable ;

raise a syntax error.
I don't find any "DROP CONSTRAINT" clause in ALTER TABLE syntax in MySql documentation.
Where I'm wrong?

 

[TonyGroves]

You would use:
[tt]
ALTER TABLE mytable
DROP INDEX un_mytable;
ALTER TABLE mytable
ADD CONSTRAINT un_mytable UNIQUE (field1,field2);
[/tt]
You might want to lock the table before starting.

 

[HelMaedb]

Thank you, very helpfull. I used [tt]DROP KEY[/tt] instead of [tt]DROP INDEX[/tt] and it worked.
Using [tt]SHOW CREATE TABLE[/tt] I noted that unique key name don't match with constraint name used in [tt]ADD CONSTRAINT[/tt] clause, but with 'field1' (I guess it's generated internally).
I see that is because I used

[tt]ALTER TABLE MyTable
ADD CONSTRAINT UN_MyTable UNIQUE (field1); [/tt]

to create the constraint.
This time I used

[tt]ALTER TABLE MyTable
ADD UNIQUE KEY UN_MyTable (field1, field2); [/tt]

to create a key that match the name, so in the future will be easier to modify it.