Liquibase: How to drop unique constraint with no constraint name?

Question

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?

http://www.liquibase.org/documentation/changes/drop_unique_constraint.html — xenteros, Jul 11 '17 at 11:44

score 5 · Answer 1 · answered Feb 23 '17 at 14:41

5

I ended up creating a new column to replace the column that has the 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"/>

answered Feb 23 '17 at 14:41

Dan Bradley

59
1
2

That's the right way, but don't forget to migrate data first. So you'll end up like: Add Column -> Update Column Data -> Drop old column. I think this behavior is made because it also works on Databases like DB2, which does not support an alter column statement. – Pwnstar Jul 11 '17 at 11:31
3

It works for DBAs that are able to drop column correctly together with indices, foreign keys etc. MSSQL is not one of them, so that 'trick' is DB-dependent. – 9ilsdx 9rvj 0lo Jan 26 '18 at 07:50

score 3 · Answer 2 · answered Nov 10 '15 at 10:42

3

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.

answered Nov 10 '15 at 10:42

Claus Radloff

357
1
11

I don't suggest using tax with a native sql statement. It causes portability problems – Pwnstar Jul 11 '17 at 11:29

score 2 · Answer 3 · edited Aug 14 '17 at 10:29

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>

This is really helpful. Your answer solved my problem. Thanks — duracell, Nov 19 '19 at 10:29

Ousama · Answer 4 · 2021-08-11T08:13:56.100

I solved this problem using the following code :

<changeSet id="202112050129-1" author="ousama">
    <sql>
        Declare @Cons_Name NVARCHAR(100)
        Declare @Str NVARCHAR(500)
        SELECT @Cons_Name=name FROM sys.objects WHERE type='UQ' AND OBJECT_NAME(parent_object_id) = N'tableName'
        SET @Str='ALTER TABLE tableName DROP CONSTRAINT ' + @Cons_Name
        Exec (@Str)
    </sql>
</changeSet>

Hope this will help you

score 1 · Answer 5 · answered Oct 23 '18 at 12:55

This is an old question, but nonetheless. For me works when I use column name as a constraint name: (MySQL)

<changeSet id="remove_unique_column_constraint" author="author">
        <dropUniqueConstraint schemaName="xxx"
                              tableName="xxx"
                              constraintName="column_name"/>
        <rollback>
               <addUniqueConstraint schemaName="xxx"
                             tableName="xxx"
                             columnNames="some_id"
                             constraintName="constraint_name"/>
         </rollback>
</changeSet>

In rollback you can set previous constraint name or new. But if in rollback you will set new constraintName, you should make rollback and set this name in dropUniqueConstraint command.

score 0 · Answer 6 · answered Jul 11 '17 at 11:41

The common way is:

Create a new column
Migrate the data to the new column (Update)

Delete the 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 like the old original column just repeat these steps by changing the names for the columns.

for step 1, set nullable to true, then add a not-null constraint after migrating the data — gachokaeric, Mar 17 '22 at 03:22

score 0 · Answer 7 · answered Nov 24 '18 at 09:56

In liquibase, there is problem with dropUniqueConstraint with some DB like postgres 9.6.6 aurora so for drop unique constraint use following:

- changeSet:
    id: dropunique_constraints
    author: ankitaggarwal
    changes:
    - dropForeignKeyConstraint:
        baseTableName: member_account
        constraintName: composite_unique_member_account

or

- changeSet:
    id: dropunique_constraints
    author: ankitaggarwal
    changes:
    - sql:
       sql: "ALTER TABLE member_account DROP CONSTRAINT composite_unique_member_account"

score 0 · Answer 8 · edited Dec 01 '21 at 04:24

I have had the same issue and I didn't want to drop the column and create a new one. Basically the question for me was How to find the Constraint Name for the Unique Key Constraint.

Here is how I found the Unique Key Constraint Name on MySQL WorkBench:

Open the Table Inspector for the table person.
Click on DDL tab.
You should see the DDL statement for the table. Something like this:

CREATE TABLE `person` (
`id` varchar(6) NOT NULL,
`name` varchar(30) NOT NULL,
...
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name `),
...
)

For me, the name of the column and the name of the unique key constraint associated were same.

In any case the structure is like this: UNIQUE KEY 'CONSTRAINT_NAME' ('COLUMN_NAME')

I ended up with this changeSet:

<changeSet author="rockstar" id="dropUniqueConstraint-example">
    <dropUniqueConstraint 
            constraintName="name"
            schemaName="public"
            tableName="person"
            uniqueColumns="name"/>
</changeSet>

I didn't have to specify the catalogName.

Amir Shaikh · Answer 9 · 2023-05-10T11:35:35.843

Follow Below Approach.

STEP 1: Find the name of the constraint by querying the database metadata

SELECT constraint_name, table_name FROM 
information_schema.table_constraints
WHERE table_name = 'your_table_name' AND constraint_type = 'UNIQUE';

STEP 2: Drop the constraint (Replace your_constraint_name and your_table_name with the appropriate values.)

<changeSet author="yourname" id="drop-unique-constraint">
    <dropUniqueConstraint constraintName="your_constraint_name" tableName="your_table_name"/>
</changeSet>

score -1 · Answer 10 · answered Jul 21 '14 at 19:04

-1

I think your best bet is to query your information schema database to find what the name is, then use that to delete it with a ChangeLog

This worked for me:

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

This would, of course, return every Unique Constraint name in your db, but you should be able to narrow your search from there.

answered Jul 21 '14 at 19:04

Michael Parker

12,724
5
37
58

See this question for more details: http://stackoverflow.com/questions/5499574/how-to-drop-a-unique-constraint-from-table-column – SteveDonie Jul 22 '14 at 16:07
2

He asked for a liquibase solution. Everybody knows how to hack it into database.... – Pwnstar Jul 11 '17 at 11:28

Liquibase: How to drop unique constraint with no constraint name?

10 Answers10

Follow Below Approach.