Liquibase drop constraint without knowing it's name

Question

We use liquibase to keep track of our database changes.. First changeSet contains those lines:

<column name="SHORT_ID" type="INTEGER">
   <constraints unique="true" />
</column>

Basically it means that SHORT_ID column has unique constraint but the name of this constraint can be whatever and usually is different each time (we run some integration tests against H2 databases and new bases are made each time we run tests)

So.. problem is: I can't change this first changeSet but now we have to get rid of this unique constraint. Any ideas how to achieve that by using liquibase?

Answer 1

Liquibase provides an implementation for dropping a not null constraint without knowing the constraint name. It may not have existed when this question was asked (I realise it's quite old).

dropNotNullConstraint

<dropNotNullConstraint catalogName="cat"
            columnDataType="int"
            columnName="id"
            schemaName="public"
            tableName="person"/>

A dropUniqueConstraint exists but you probably already knew about it as it requires the constraint name.

Answer 2

H2's SQL for dropping constraints requires a constraint name. I don't remember if the auto-generated constraint name in H2 is random or would be consistent across databases.

If it's constant, you can use the normal liquibase tag and it will work fine.

If it's random, you will have to get the constraint name from the information_schema. H2 may allow something like:

alter table TABLE_NAME drop constraint 
      (select unique_index_name 
              from information_schema.constraints 
              where table_name='TABLE_NAME' and column_name='SHORT_ID')

If not, you may need to create a custom liquibase change (http://liquibase.org/extensions with 2.0, http://www.liquibase.org/manual/custom_refactoring_class in 1.9) that makes the call and drops the constraint.