Liquibase drop constraint without knowing it's name Liquibase drop constraint without knowing it's name sql sql

Liquibase drop constraint without knowing it's name


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.


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.


For HSQL the query which Nathan suggested does not work (alter table TABLE_NAME drop constraint (select unique_index_name from information_schema.constraints where table_name='TABLE_NAME' and column_name='SHORT_ID'))

This is because, DDLs and SQLs can not be mixed.

Dropping constraint by knowing the name at runtime does not seems to be possible with HSQL (I need this in rollbacks before dropping columns with constraints). This is possible for Oracle and MSSQL.

What I do is get a generated databse through hbm files (hibernate). As hibernate does not support naming unique key constraints, so for e.g. dropping these columns with constraints is a not possible using liquibase (for HSQL). We have tests based on HSQL testing the whole stuff. Would be good to get a solution for HSQL.

For now, I just hard code the Unique constraint names (a work around)