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.

I am not sure if I understand what you're asking - but 2 things to consider: Let liquibase run with `updateSQL` instead of `update`. It will produce the SQL statements rather than running them directly on the database. This way you can check what liquibase really generates. The second thing is: I am also using liquibase and hibernate and the constraint names look a lot like hibernate generated. So are you sure your table has really been generated by liquibase? — Jens, May 24 '16 at 17:56

score 1 · Accepted Answer · edited Mar 20 '17 at 10:29

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)

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

1 Answers1