Geek Answers Handbook

Liquibase: How to remove a unique constraint without a constraint name?

This is what my column looks like

<column name="name" type="VARCHAR(255)"> <constraints nullable="false" unique="true"/> </column>

I want to remove the unique=true constraint.

I looked what liquibase has to offer, and it has

 <changeSet author="liquibase-docs" id="dropUniqueConstraint-example"> <dropUniqueConstraint catalogName="cat" constraintName="const_name" schemaName="public" tableName="person" uniqueColumns="A String"/> </changeSet>

Now since constraintName is required and I don't have one, what are my options?

How to reset unique=true with liquibase?

+7
java liquibase
source share
5 answers

You can execute SQL like this, but it may not work in all databases:

 <sql>alter table Person drop unique (name)</sql>

If there is an explicitly created index in this column, you can also discard it:

 <sql>alter table Person drop unique (name) drop index</sql>

This works for Oracle, other databases may have different syntax.

+2
source share

As a result, I created a new column to replace the column with a unique constraint.

 <addColumn tableName="TABLE" schemaName="SCHEMA"> <column name="NEW_COLUMN" type="TYPE" valueComputed="OLD_COLUMN"></column> </addColumn> <dropColumn tableName="TABLE" schemaName="SCHEMA" columnName="OLD_COLUMN"/> <renameColumn tableName="TABLE" schemaName="SCHEMA" oldColumnName="NEW_COLUMN" newColumnName="OLD_COLUMN"/>
+2
source share

If you are using postgresql, you can find the name constraintName in pgAdmin. Select the column, then go to the fourth tab on the right (I think it will be called "dependencies" or something similar)

enter image description here

As you can see, in my case constraintName is "public.external_message_storage_message_external_uuid_key". Then just

  <changeSet author="me" id="dropping constraint"> <dropUniqueConstraint constraintName="external_message_storage_message_external_uuid_key" schemaName="public" tableName="external_message_storage" uniqueColumns="message_external_uuid"/> </changeSet>
+1
source share

General way:

  • Create new column
  • Move data to a new column (Refresh)

  • Delete old column

     <changeSet id="20170711-001" author="yourName"> <addColumn tableName="tableName"> <column name="name2" type="VARCHAR(255)"> <constraints nullable="false" unique="false" /> </column> </addColumn> </changeSet> <changeSet author="yourName" id="20170711-002"> <update tableName="tableName"> <column name="name2" type="varchar(255)" valueComputed="originalColumn"/> </update> </changeSet> <changeSet author="yourName" id="20170711-003"> <dropColumn columnName="originalColumn" tableName="tableName"/> </changeSet>

If you want to rename the new column as the old source column, simply repeat these steps by changing the column names.

0
source share

I think your best bet is to query the database of your information schema to find out what the name is and then use it to delete it using ChangeLog

This worked for me:

 SELECT `CONSTRAINT_NAME` FROM `TABLE_CONSTRAINTS` WHERE `CONSTRAINT_TYPE` LIKE 'UNIQUE'

This, of course, will return every unique Constraint name in your db, but you can narrow your search from there.

-2
source share

More articles:


All Articles