Search code examples
sqlhsqldbunique-constraint

HSQLDB and unique constraint

After many researches, i didn't find how to deal with unique constraint with null columns for HSQLDB.

This question is directly linked with: Create unique constraint with null columns.

Related answers (workaround) work fine with PostgreSQL (create unique index ... where conditions), but not with HSQLDB (from my point of view).

Ideally, I'd like to have the same "create_ddl" script for PostgreSQL and HSQLDB that manages the unique constraint behaviour with null columns

Any help, idea or workaround would be appreciated.

Solution

  • HSQLDB has a setting to allow only one row with a null in one of the columns of a UNIQUE constraint. You can change the default setting using this statement once.

    SET DATABASE SQL UNIQUE NULLS FALSE

    You simply create a UNIQUE constraint on the three columns. The null is treated as a distinct value and it will not allow two rows that have the same values in the three columns even when one of them is null.