Remove duplicates for unique constraint

Question

There's a cars table in my database with column model_name. I need to add the unique constraint on model_name column, but there are a lot of duplicate values in it.

That's why if I try <addUniqueConstraint>, my changeSet fails. So is there any way of removing all duplicates before adding unique constraint?

Thanks for you help.

Answer 1

In accordance with liquibase official documentation it's not possible out-of-the-box.

You can remove all duplicates with <sql> tag, and then use <addUniqueConstraint>.

E.G.:

<changeSet author="changeset-author" id="changeset-id">
    <preConditions onFail="MARK_RAN">
        <columnExists tableName="cars" columnName="model_name"/>
    </preConditions>
    <sql>
        DELETE c1 FROM cars c1, cars c2 WHERE c1.id > c2.id AND c1.model_name = c2.model_name;
    </sql>
    <!--or as was suggested by @JavaDevSweden-->
    <sql>
        DELETE FROM cars WHERE id in (SELECT c1.id FROM cars c1, cars c2 WHERE c1.id > c2.id AND c1.model_name = c2.model_name);
    </sql>
    <addUniqueConstraint tableName="cars" columnNames="model_name" constraintName="cars_model_name_unique"/>
</changeSet>