CODEWITHSUNDEEP
sqlliquibasepreconditions
siebz0r
siebz0r

Reputation: 20339

Liquibase precondition for unique constraint

I want to add a unique constraint to a column using Liquibase. Of course I want to check if duplicate rows exist using a precondition.

I came up this:

<preConditions>
    <sqlCheck expectedResult="0">
        select count(*)
        from person
        having ( count(username) > 1 )
    </sqlCheck>
</preConditions>

However this produces Empty set on MySQL and probably other databases.

I tried using expectedResult="" and expectedResult="null" but both don't work.

Upvotes: 3

Views: 6329

Answers (1)

Bohemian
Bohemian

Reputation: 425003

You could always force a result:

select 
  case when exists(
    select username, count(*)
    from person
    group by username
    having count(*) > 1 )
    then 1
    else 0
  end

This also allows a more normal group by/having

Upvotes: 8

Related Questions