liquibase - how to alter table to make a existing constraint unique?

Question

I’m using hibernate4 hbm2ddl to check a mysql schema is up to date. It is telling me table needs changed :

alter table ems.FooMetaData 
    drop constraint UK_jbjctqk7dys3q0xr5brfsdjvv
alter table ems.FooMetaData 
    add constraint UK_jbjctqk7dys3q0xr5brfsdjvv unique (metaDataId)

The table was looks like this in MySQL:

mysql> show create table FooMetaData;
| FooMetaData | CREATE TABLE `FooMetaData` (
  `fooMemberId` int(11) DEFAULT NULL,
  `metaDataId` varchar(255) DEFAULT NULL,
  KEY `FK948B6448448663FC` (`metaDataId`),
  KEY `FK948B64486FA1E99D` (`fooMemberId`),
  CONSTRAINT `FK948B6448448663FC` FOREIGN KEY (`metaDataId`) REFERENCES `BarValues` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `FK948B64486FA1E99D` FOREIGN KEY (`fooMemberId`) REFERENCES `Foos` (`fooMemberId`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

It was created like this using Liquibase (version 3.4.2)

  <changeSet author="myauthor" id="myid">
          <addForeignKeyConstraint baseColumnNames="metaDataId" 
           baseTableName="FooMetaData" baseTableSchemaName="foo" constraintName="FK948B6448448663FC"                                                                     
           deferrable="false" initiallyDeferred="false" onDelete="NO ACTION" onUpdate="NO ACTION" 
            referencedColumnNames="id" referencedTableName="BarValues" 
            referencedTableSchemaName="foo" referencesUniqueColumn="false"/>
        </changeSet>

How to update constraint with a unique (metaDataId) ?

I tried deleting that constraint in hbm2ddl in liquibase and adding with referencesUniqueColumn="true" but looks same.

Answer 1

It was a XY problem; in case someone else goes down same path as I did here is what I learned ...

The real root-cause was the tables (and constraints) were created (using Liquibase) to comply with an app built with older version of hibernate (v3).

We upgraded app to hibernate4, and its hbm2ddl test complained about some of the constaints being different in the app’s annotations vs the real database.

From the hbm2ddl error message, I'd thought it was the unique suffix that was causing the problem, however, it was simply that the constraint name needed changed remove the constaint and re-add it exactly as was except with the desired constraintName.

So I made progress but in liquibase ....

    <dropForeignKeyConstraint baseTableName="FooMetaData" constraintName="FK948B6448448663FC"/>

 <addForeignKeyConstraint 
  // SNIP        
    constraintName="UK_jbjctqk7dys3q0xr5brfsdjvv"
 // SNIP same attributes as original addForeignKeyConstraint 

and I didn’t need to change anything to do with unique (metaDataId)