Search code examples
nullconstraintsuniqueh2default

Error when trying to set a UNIQUE constraint with DEFAULT NULL in H2 database

I would like to create a table with a column with a UNIQUE constraint and DEFAULT NULL, see the annotation_id column in the below code snippet. This works in Postgres but fails in H2 but according to the documentation, it should work (http://www.h2database.com/html/grammar.html#column_definition).

Here is the DDL script:

CREATE TABLE highlights (
   id bigserial PRIMARY KEY NOT NULL,
   user_ref_id uuid,
   body varchar(10000) DEFAULT NULL,
   annotation_id integer UNIQUE DEFAULT NULL
);

This is the error I'm getting:

Syntax error in SQL statement "CREATE TABLE HIGHLIGHTS2 (
   ID BIGSERIAL PRIMARY KEY NOT NULL,
   USER_REF_ID UUID,
   BODY VARCHAR(10000) DEFAULT NULL,
   ANNOTATION_ID INTEGER UNIQUE DEFAULT[*] NULL
)"; expected "NOT, NULL, COMMENT, CHECK, REFERENCES, ,, )"; SQL statement:
CREATE TABLE highlights2 (
   id bigserial PRIMARY KEY NOT NULL,
   user_ref_id uuid,
   body varchar(10000) DEFAULT NULL,
   annotation_id integer UNIQUE DEFAULT NULL
) [42001-200] 42001/42001

According to this thread here, this should be possible: https://groups.google.com/g/h2-database/c/HrYKGMN2LPQ?pli=1

H2 already supports NULL in unique constraints.

What am I missing?

Solution

    1. You cannot specify a column constraint (UNIQUE or any other) in the middle of definition of a column, such constraint can be written only at the end of column's definition: annotation_id integer DEFAULT NULL UNIQUE.

    2. DEFAULT NULL is a meaningless clause, all nullable columns will be assigned to NULL with or without this clause if other value wasn't specified. This clause can be removed. You need to use the DEFAULT clause only when you want to specify some other value as default.