CREATE TABLE succeeds or fails depending on the order of the CONSTRAINT statements

[stijn-vranckx]

For H2 versions 2.1.210 the following query works:

CREATE TABLE "meta_data"
(
"meta_data_id" BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL,
"identifier" VARCHAR(255) NOT NULL,
"parent_identifier" VARCHAR(255),
CONSTRAINT "meta_data_pk" PRIMARY KEY ("meta_data_id"),
CONSTRAINT "meta_data_parent_identifier_fk" FOREIGN KEY ("parent_identifier") REFERENCES "meta_data" ("identifier"),
CONSTRAINT "meta_data_identifier_uc" UNIQUE ("identifier")
);

where the next query fails:

CREATE TABLE "meta_data"
(
"meta_data_id" BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL,
"identifier" VARCHAR(255) NOT NULL,
"parent_identifier" VARCHAR(255),
CONSTRAINT "meta_data_pk" PRIMARY KEY ("meta_data_id"),
CONSTRAINT "meta_data_identifier_uc" UNIQUE ("identifier"),
CONSTRAINT "meta_data_parent_identifier_fk" FOREIGN KEY ("parent_identifier") REFERENCES "meta_data" ("identifier")
);

The only difference being the order of the two last CONSTRAINTS.

For H2 versions 1.4.196 both queries succeed.

Is there a reason why second query has stopped working?

[andreitokar]

I tried both in 2.2.224 and first one is failing, but second works.

 [90057][90057] Constraint "PRIMARY KEY | UNIQUE (identifier)" not found; SQL statement:
CREATE TABLE "meta_data"
(
"meta_data_id" BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL,
"identifier" VARCHAR(255) NOT NULL,
"parent_identifier" VARCHAR(255),
CONSTRAINT "meta_data_pk" PRIMARY KEY ("meta_data_id"),
CONSTRAINT "meta_data_parent_identifier_fk" FOREIGN KEY ("parent_identifier") REFERENCES "meta_data" ("identifier"),
CONSTRAINT "meta_data_identifier_uc" UNIQUE ("identifier")
) [90057-224]

It makes much more sense to me, because foreign key constraint assume existence of a unique key.

[stijn-vranckx]

It is possible that I switched them around by accident.
However we are using liquibase to generate our tables.
And after the upgrade from H2 to the latest version, where we previously used 1.x we starting running into this issue.

The problem does not occur on other database platforms we are using.

[katzyn]

I think there is a bug in H2, but inline referential constraints in table definitions are always problematic even when there are no bugs in the database system itself.

At least some frameworks always create them only after creation of all tables to avoid all kinds of issues here (such as execution of table creation scripts in a wrong order and others).