1

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.

Cédric Gecko
  • 55
  • 1
  • 9

1 Answers1

1

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.

fredt
  • 24,044
  • 3
  • 40
  • 61
  • Thanks a lot, fredt! That totally matches my need. By the way, i'd like to create an unique constraint with condition other than null value (e.g. 'where =true'). How can i succeed with HSQLDB ? Have i to create a new question for this ? – Cédric Gecko Jan 04 '18 at 13:31
  • I don't know what you mean. If in two rows you have (3, true) and (3, true) in the constraint columns they are not distinct and cannot be both inserted. Write examples of values you want to be distinct. – fredt Jan 05 '18 at 18:01
  • Thanks for your answer. I'd like to insert row (3,false) several times, but i'd like to insert only once, row (3, true). In PostgreSql, I defined this behaviour this way : CREATE UNIQUE INDEX pmd_unique_def_wlt_idx ON TABLE_A (COLUMN_ID,DEFAULT_USE) WHERE DEFAULT_USE=true; – Cédric Gecko Jan 09 '18 at 08:42
  • You can define a generated column and create the constraint on this column `COL_ID INT GENERATED ALWAYS AS (CASE WHEN DEFAULT_USE THEN COLUMN_ID ELSE NULL)` but it relies on the default UNIQUE NULLS TRUE behaviour. – fredt Jan 10 '18 at 02:22