SQL generated by Liquibase is not accepted by H2 because because foreign key constraint assume existence of a unique key.

[stijn-vranckx]

Search first

• I searched and no similar issues were found

Description

The order of the statements in the create table is incorrect.

Jan 12, 2024 9:28:31 AM liquibase.database
INFO: Set default schema name to PUBLIC
Jan 12, 2024 9:28:32 AM liquibase.changelog
INFO: Creating database history table with name: PUBLIC.DATABASECHANGELOG
Jan 12, 2024 9:28:32 AM liquibase.changelog
INFO: Reading from PUBLIC.DATABASECHANGELOG
Jan 12, 2024 9:28:32 AM liquibase.lockservice
INFO: Successfully acquired change log lock
Jan 12, 2024 9:28:32 AM liquibase.command
INFO: Using deploymentId: 5048112321
Jan 12, 2024 9:28:32 AM liquibase.changelog
INFO: Reading from "PUBLIC"."DATABASECHANGELOG"
Jan 12, 2024 9:28:32 AM liquibase.ui
INFO: Running Changeset: master.xml::20161025-1512a::studio
Jan 12, 2024 9:28:32 AM liquibase.changelog
SEVERE: ChangeSet master.xml::20161025-1512a::studio encountered an exception.
Jan 12, 2024 9:28:32 AM liquibase.util
INFO: UPDATE SUMMARY
Jan 12, 2024 9:28:32 AM liquibase.util
INFO: Run: 1
Jan 12, 2024 9:28:32 AM liquibase.util
INFO: Previously run: 0
Jan 12, 2024 9:28:32 AM liquibase.util
INFO: Filtered out: 0
Jan 12, 2024 9:28:32 AM liquibase.util
INFO: -------------------------------
Jan 12, 2024 9:28:32 AM liquibase.util
INFO: Total change sets: 1
Jan 12, 2024 9:28:32 AM liquibase.util
INFO: Update summary generated
Jan 12, 2024 9:28:32 AM liquibase.command
INFO: Update command encountered an exception.
Jan 12, 2024 9:28:32 AM liquibase.lockservice
INFO: Successfully released change log lock
Jan 12, 2024 9:28:32 AM liquibase.command
INFO: Logging exception.
Jan 12, 2024 9:28:32 AM liquibase.ui
INFO: ERROR: Exception Details
Jan 12, 2024 9:28:32 AM liquibase.ui
INFO: ERROR: Exception Primary Class: JdbcSQLSyntaxErrorException
Jan 12, 2024 9:28:32 AM liquibase.ui
INFO: ERROR: Exception Primary Reason: Constraint "PRIMARY KEY | UNIQUE (identifier)" not found; SQL statement:
CREATE TABLE "PUBLIC"."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 "PUBLIC"."meta_data"("identifier"), CONSTRAINT "meta_data_identifier_uc" UNIQUE ("identifier")) [90057-224]
Jan 12, 2024 9:28:32 AM liquibase.ui
INFO: ERROR: Exception Primary Source: H2 2.2.224 (2023-09-17)
Jan 12, 2024 9:28:32 AM liquibase.command
INFO: Command execution complete
Exception in thread "main" liquibase.exception.UnexpectedLiquibaseException: liquibase.exception.CommandExecutionException: liquibase.exception.LiquibaseException: liquibase.exception.MigrationFailedException: Migration failed for changeset master.xml::20161025-1512a::studio:
Reason: liquibase.exception.DatabaseException: Constraint "PRIMARY KEY | UNIQUE (identifier)" not found; SQL statement:
CREATE TABLE "PUBLIC"."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 "PUBLIC"."meta_data"("identifier"), CONSTRAINT "meta_data_identifier_uc" UNIQUE ("identifier")) [90057-224] [Failed SQL: (90057) CREATE TABLE "PUBLIC"."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 "PUBLIC"."meta_data"("identifier"), CONSTRAINT "meta_data_identifier_uc" UNIQUE ("identifier"))]
at liquibase.integration.spring.SpringLiquibase.afterPropertiesSet(SpringLiquibase.java:304)
at Tost.main(Tost.java:15)
Caused by: liquibase.exception.CommandExecutionException: liquibase.exception.LiquibaseException: liquibase.exception.MigrationFailedException: Migration failed for changeset master.xml::20161025-1512a::studio:
Reason: liquibase.exception.DatabaseException: Constraint "PRIMARY KEY | UNIQUE (identifier)" not found; SQL statement:
CREATE TABLE "PUBLIC"."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 "PUBLIC"."meta_data"("identifier"), CONSTRAINT "meta_data_identifier_uc" UNIQUE ("identifier")) [90057-224] [Failed SQL: (90057) CREATE TABLE "PUBLIC"."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 "PUBLIC"."meta_data"("identifier"), CONSTRAINT "meta_data_identifier_uc" UNIQUE ("identifier"))]
at liquibase.command.CommandScope.execute(CommandScope.java:253)
at liquibase.Liquibase.lambda$update$0(Liquibase.java:245)
at liquibase.Scope.lambda$child$0(Scope.java:186)
at liquibase.Scope.child(Scope.java:195)
at liquibase.Scope.child(Scope.java:185)
at liquibase.Scope.child(Scope.java:164)
at liquibase.Liquibase.runInScope(Liquibase.java:1419)
at liquibase.Liquibase.update(Liquibase.java:234)
at liquibase.Liquibase.update(Liquibase.java:212)
at liquibase.integration.spring.SpringLiquibase.performUpdate(SpringLiquibase.java:343)
at liquibase.integration.spring.SpringLiquibase.lambda$afterPropertiesSet$0(SpringLiquibase.java:294)
at liquibase.Scope.lambda$child$0(Scope.java:186)
at liquibase.Scope.child(Scope.java:195)
at liquibase.Scope.child(Scope.java:185)
at liquibase.Scope.child(Scope.java:164)
at liquibase.Scope.child(Scope.java:252)
at liquibase.integration.spring.SpringLiquibase.afterPropertiesSet(SpringLiquibase.java:287)
... 1 more
Caused by: liquibase.exception.LiquibaseException: liquibase.exception.MigrationFailedException: Migration failed for changeset master.xml::20161025-1512a::studio:
Reason: liquibase.exception.DatabaseException: Constraint "PRIMARY KEY | UNIQUE (identifier)" not found; SQL statement:
CREATE TABLE "PUBLIC"."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 "PUBLIC"."meta_data"("identifier"), CONSTRAINT "meta_data_identifier_uc" UNIQUE ("identifier")) [90057-224] [Failed SQL: (90057) CREATE TABLE "PUBLIC"."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 "PUBLIC"."meta_data"("identifier"), CONSTRAINT "meta_data_identifier_uc" UNIQUE ("identifier"))]
at liquibase.changelog.ChangeLogIterator.run(ChangeLogIterator.java:151)
at liquibase.command.core.AbstractUpdateCommandStep.lambda$run$0(AbstractUpdateCommandStep.java:110)
at liquibase.Scope.lambda$child$0(Scope.java:186)
at liquibase.Scope.child(Scope.java:195)
at liquibase.Scope.child(Scope.java:185)
at liquibase.Scope.child(Scope.java:164)
at liquibase.command.core.AbstractUpdateCommandStep.run(AbstractUpdateCommandStep.java:108)
at liquibase.command.core.UpdateCommandStep.run(UpdateCommandStep.java:105)
at liquibase.command.CommandScope.execute(CommandScope.java:217)
... 17 more
Caused by: liquibase.exception.MigrationFailedException: Migration failed for changeset master.xml::20161025-1512a::studio:
Reason: liquibase.exception.DatabaseException: Constraint "PRIMARY KEY | UNIQUE (identifier)" not found; SQL statement:
CREATE TABLE "PUBLIC"."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 "PUBLIC"."meta_data"("identifier"), CONSTRAINT "meta_data_identifier_uc" UNIQUE ("identifier")) [90057-224] [Failed SQL: (90057) CREATE TABLE "PUBLIC"."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 "PUBLIC"."meta_data"("identifier"), CONSTRAINT "meta_data_identifier_uc" UNIQUE ("identifier"))]
at liquibase.changelog.ChangeSet.execute(ChangeSet.java:797)
at liquibase.changelog.visitor.UpdateVisitor.executeAcceptedChange(UpdateVisitor.java:119)
at liquibase.changelog.visitor.UpdateVisitor.visit(UpdateVisitor.java:68)
at liquibase.changelog.ChangeLogIterator$2.lambda$run$0(ChangeLogIterator.java:133)
at liquibase.Scope.lambda$child$0(Scope.java:186)
at liquibase.Scope.child(Scope.java:195)
at liquibase.Scope.child(Scope.java:185)
at liquibase.Scope.child(Scope.java:164)
at liquibase.changelog.ChangeLogIterator$2.run(ChangeLogIterator.java:122)
at liquibase.Scope.lambda$child$0(Scope.java:186)
at liquibase.Scope.child(Scope.java:195)
at liquibase.Scope.child(Scope.java:185)
at liquibase.Scope.child(Scope.java:164)
at liquibase.Scope.child(Scope.java:252)
at liquibase.Scope.child(Scope.java:256)
at liquibase.changelog.ChangeLogIterator.run(ChangeLogIterator.java:89)
... 25 more
Caused by: liquibase.exception.DatabaseException: Constraint "PRIMARY KEY | UNIQUE (identifier)" not found; SQL statement:
CREATE TABLE "PUBLIC"."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 "PUBLIC"."meta_data"("identifier"), CONSTRAINT "meta_data_identifier_uc" UNIQUE ("identifier")) [90057-224] [Failed SQL: (90057) CREATE TABLE "PUBLIC"."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 "PUBLIC"."meta_data"("identifier"), CONSTRAINT "meta_data_identifier_uc" UNIQUE ("identifier"))]
at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:470)
at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:77)
at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:179)
at liquibase.database.AbstractJdbcDatabase.execute(AbstractJdbcDatabase.java:1291)
at liquibase.database.AbstractJdbcDatabase.executeStatements(AbstractJdbcDatabase.java:1273)
at liquibase.changelog.ChangeSet.execute(ChangeSet.java:755)
... 40 more
Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Constraint "PRIMARY KEY | UNIQUE (identifier)" not found; SQL statement:
CREATE TABLE "PUBLIC"."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 "PUBLIC"."meta_data"("identifier"), CONSTRAINT "meta_data_identifier_uc" UNIQUE ("identifier")) [90057-224]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:644)
at org.h2.message.DbException.getJdbcSQLException(DbException.java:489)
at org.h2.message.DbException.get(DbException.java:223)
at org.h2.message.DbException.get(DbException.java:199)
at org.h2.command.ddl.AlterTableAddConstraint.tryUpdate(AlterTableAddConstraint.java:259)
at org.h2.command.ddl.AlterTableAddConstraint.update(AlterTableAddConstraint.java:74)
at org.h2.command.ddl.AlterTable.update(AlterTable.java:46)
at org.h2.command.ddl.CommandWithColumns.createConstraints(CommandWithColumns.java:83)
at org.h2.command.ddl.CreateTable.update(CreateTable.java:134)
at org.h2.command.CommandContainer.update(CommandContainer.java:169)
at org.h2.command.Command.executeUpdate(Command.java:256)
at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:262)
at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:231)
at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:464)
... 45 more

Generates the following query failing query:

CREATE TABLE "PUBLIC"."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 "PUBLIC"."meta_data" ("identifier"),
    CONSTRAINT "meta_data_identifier_uc" UNIQUE ("identifier")
)

Where the following query works correctly:

CREATE TABLE "PUBLIC"."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 "PUBLIC"."meta_data" ("identifier")
)

Steps To Reproduce

Run the following code:

import liquibase.exception.LiquibaseException;
import liquibase.integration.spring.SpringLiquibase;
import org.h2.jdbcx.JdbcDataSource;

public class Tost {

  public static void main(String[] args) throws LiquibaseException {
    JdbcDataSource dataSource = new JdbcDataSource();
    dataSource.setPassword("password");
    dataSource.setUser("sa");
    dataSource.setURL("jdbc:h2:mem:testdb");
    SpringLiquibase springLiquibase = new SpringLiquibase();
    springLiquibase.setChangeLog("master.xml");
    springLiquibase.setDataSource(dataSource);
    springLiquibase.afterPropertiesSet();
  }

}

with master.xml

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog objectQuotingStrategy="QUOTE_ALL_OBJECTS"
                   xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
                   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                   xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.1.xsd">

  <property name="quot" value="&quot;"/>

  <changeSet id="20161025-1512a" author="studio">
    <createTable tableName="meta_data">
      <column name="meta_data_id" type="BIGINT" autoIncrement="true">
        <constraints nullable="false" primaryKey="true" primaryKeyName="meta_data_pk" uniqueConstraintName="meta_data_id_uc"/>
      </column>
      <column name="identifier" type="VARCHAR(255)" >
        <constraints nullable="false" unique="true" uniqueConstraintName="meta_data_identifier_uc"/>
      </column>
      <column name="parent_identifier" type="VARCHAR(255)">
        <constraints nullable="true" foreignKeyName="meta_data_parent_identifier_fk" references="${quot}meta_data${quot}(${quot}identifier${quot})"/>
      </column>
    </createTable>
  </changeSet>

</databaseChangeLog>

and pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>

  <groupId>org.example</groupId>
  <artifactId>Liqui</artifactId>
  <version>1.0-SNAPSHOT</version>

  <properties>
    <maven.compiler.source>8</maven.compiler.source>
    <maven.compiler.target>8</maven.compiler.target>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
  </properties>

  <dependencies>
    <dependency>
      <groupId>org.liquibase</groupId>
      <artifactId>liquibase-core</artifactId>
      <version>4.25.1</version>
    </dependency>

    <dependency>
      <groupId>com.h2database</groupId>
      <artifactId>h2</artifactId>
      <version>2.2.224</version>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-beans</artifactId>
      <version>5.3.28</version>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-context</artifactId>
      <version>5.3.28</version>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-jdbc</artifactId>
      <version>5.3.28</version>
    </dependency>

  </dependencies>

</project>

Expected/Desired Behavior

Generate correct SQL and table

Liquibase Version

4.25.1

Database Vendor & Version

H2 2.2.224

Liquibase Integration

No response

Liquibase Extensions

No response

OS and/or Infrastructure Type/Provider

Windows 11

Additional Context

The query is expect to fail according to H2 maintainers: see here

Are you willing to submit a PR?

• I'm willing to submit a PR (Thank you!)

[tati-qalified]

Hi @stijn-vranckx, thank you for reporting this issue.

I was able to replicate it with Liquibase versions 4.25.1 and 4.19.1, so I can confirm that it's not a new issue. We will leave this issue open for the community to propose a solution for. The dev team will be available for any guidance needed.

In the meantime, a workaround is running liquibase update-sql and manually modifying the statements.

Thank you,
Tatiana