Liquibase: How to drop unique constraint with no 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 unique=true constraint.
I looked at 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 do not have it, what are my options?
How can I drop unique=true using liquibase?
You can execute a SQL like this, but it may not work on all databases:
<sql>alter table Person drop unique (name)</sql>
If there is an explicit created index on that column, you may want to drop that too:
<sql>alter table Person drop unique (name) drop index</sql>
This works for Oracle, other databases may have a different Syntax.
我最终创建了一个新列来替换具有唯一约束的列。
<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"/>
If you using postgresql, you can find required constraintName in pgAdmin. Select you column, then go to fourth tab on the right (I believe it would be called "dependencies", or something similar)
As you can see, in my case constraintName is "public.external_message_storage_message_external_uuid_key" Then just do
<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>