Liquibase precondition for unique constraint

Question

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.

score 8 · Accepted Answer · answered Jan 14 '13 at 19:29

8

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

answered Jan 14 '13 at 19:29

Bohemian

412,405
93
575
722

Thanks, works on MySQL. Does this work on other databases like MSSQL/Oracle/HSQL? – siebz0r Jan 14 '13 at 19:35
This query will work on all databases (I composed it that way because you didn't tag your question with a specific database) – Bohemian Jan 14 '13 at 19:40
Super! Really appreciate it ;-) – siebz0r Jan 14 '13 at 20:02

Liquibase precondition for unique constraint

1 Answers1