Unique key constraint with multiple columns allowing duplicates with null in one of the columns (H2 database)

Question

I have a table created like this:

CREATE TABLE IF NOT EXISTS report (
    id IDENTITY PRIMARY KEY,
    group_id INT NOT NULL,
    group_type VARCHAR(25) NOT NULL,
    device_item_id INT NOT NULL,
    device_name TEXT NOT NULL,
    item_id INT NULL,
    item_name TEXT NULL,
    device_ip VARCHAR(45) NOT NULL
    from_tstamp TIMESTAMP NOT NULL,
    to_tstamp TIMESTAMP NOT NULL
    version INT NOT NULL

    CONSTRAINT report_unique_constraint
    UNIQUE (group_id, group_type, device_item_id, item_id, from_tstamp, to_tstamp)
);

The use case is that when the group_type is 'A', then item_id will never be set and will always be NULL. When group_type is 'B', then item_id will also have a value.

So, when group_type is 'A', the H2 database is allowing the below two records to be inserted:

1,335640,A,5316,d_name,NULL,NULL,192.168.1.1,2022-04-17 00:00:00.000000,2022-04-23 23:59:59.000000,0

2,335640,A,5316,d_name,NULL,NULL,192.168.1.1,2022-04-17 00:00:00.000000,2022-04-23 23:59:59.000000,1

To ease out reading, these are the values from the above 2 records that are part of unique key constraint:

335640,A,5316,NULL,2022-04-17 00:00:00.000000,2022-04-23 23:59:59.000000

Question: Why is NULL in item_id ignored and 2 similar records were allowed to be entered? Is there a way that NULL item_id is also considered same value when enforcing the constraint?

Workaround

A workaround I have implemented to overcome this for now is to make item_id NOT NULL in database and when the group_type is ‘A’, then insert a dummy value -99. This makes the unique constraint behave as desired. As hack it is!!!

Answer 1

NULL doesn’t equal NULL. Therefore if 2 records have the same values in a list of columns and one or more of those column values is NULL then those 2 records are not the same and so the unique constraint doesn’t stop both records being inserted into the DB