Spring Boot: database initialization fails with "Referential integrity constraint violation" on init SQL scripts

I'm trying to refine the "TacoOrder" application from "Spring in Action" a bit and am setting up database initialization for a H2 database from my Spring Boot application.

This is done by adding two data initialization scripts as described in Initialize a Database Using Basic SQL Scripts

The DDL script schema-h2.sql

DROP TABLE IF EXISTS ingredient;
DROP TABLE IF EXISTS ingredient_type;

CREATE TABLE IF NOT EXISTS ingredient_type (
    name VARCHAR(100) NOT NULL PRIMARY KEY,
    index INTEGER NOT NULL UNIQUE,
    exclusive BOOLEAN NOT NULL,
    mandatory BOOLEAN NOT NULL
);

CREATE TABLE IF NOT EXISTS ingredient (
    name VARCHAR(100) NOT NULL PRIMARY KEY, -- "enum-like" name, lower-cased
    description CHAR(100) NOT NULL,      -- a cleartext description
    type VARCHAR(100) NOT NULL,
    FOREIGN KEY(type) REFERENCES ingredient_type(name)
);

The DML script data-h2.sql

INSERT INTO ingredient_type (name, index, exclusive, mandatory)
VALUES ('wrap', 1, true, true),
       ('protein', 2, false, false),
       ('veggies', 3, false, false),
       ('cheese', 4, false, false),
       ('sauce', 5, false, true);

INSERT INTO ingredient (name, description, type)
VALUES ('flto', 'Flour Tortilla', 'wrap'),
       ('coto', 'Corn Tortilla', 'wrap'),
       ('grbf', 'Ground Beef', 'protein'),
       ('carn', 'Carnitas', 'protein'),
       ('tmto', 'Diced Tomatoes', 'veggies'),
       ('letc', 'Lettuce', 'veggies'),
       ('ched', 'Cheddar', 'cheese'),
       ('jack', 'Monterrey Jack', 'cheese'),
       ('slsa', 'Salsa', 'sauce'),
       ('srcr', 'Sour Cream', 'sauce');

The above being introduced by the appropriate entry in application.yml:

spring:
  sql:
    init:
      mode: always # also initialize for non-in-memory databases
      platform: h2 # use the script that have "h2" in their name

This is all very common.

Graphically, the database schema is:

If I run the above scripts manually (in order "schema" then "data", naturally), everything works fine and the desired pre-filled tables appear in the H2 database.

If I start the Spring Boot application, the tables appear in the H2 database but the data prefill fails with a constraint violation:

org.h2.jdbc.JdbcSQLIntegrityConstraintViolationException: Referential
   integrity constraint violation: "CONSTRAINT_10: PUBLIC.INGREDIENT
   FOREIGN KEY(TYPE) REFERENCES PUBLIC.INGREDIENT_TYPE(NAME) ('wrap')";
SQL statement:
   INSERT INTO ingredient (name, description, type) VALUES
   ('flto', 'Flour Tortilla', 'wrap'),
   ('coto', 'Corn Tortilla', 'wrap'),
   ('grbf', 'Ground Beef', 'protein'), ... [23506-220]

In the application log, we see:

Exception encountered during context initialization - cancelling refresh attempt ...
Failed to execute SQL script statement #2 of file [.../data-h2.sql]

I surmise that for some reason the filling of table ingredient_type is not done, or skipped. But why?

In fact, if I remove the FOREIGN KEY constraint, I end up with a filled ìngredient table and an empty ingredient_type table. Then the application still doesn't run because there is no data of course.

Experiment #1

Another trial whereby I add the definition and data for a third table, while leaving out the FOREIGN KEY constraint, like this:

CREATE TABLE IF NOT EXISTS taco (
    id INTEGER NOT NULL PRIMARY KEY,
    when_created DATETIME,
    when_updated DATETIME
)

INSERT INTO taco (id, when_created, when_updated)
VALUES (1, '2023-01-01 12:00:00', '2023-01-01 13:00:00');

...results in tables ingredient and taco filled, and ingredient_type not filled. Looks like there is something peculiar about that table? But again, running the DDL DML scripts manually works fine.

Experiment #2

Aha, it seems just that the first INSERT in the DML file is being ignored by Spring Boot. If one moves the statement to fill table ingredient to the top of the DML file, it is table ingredient that remains empty. Similarly moving the statement to fill table taco to the top means that taco gets the silent treatment. But why!

Can you try adding the following property to your configuration:

spring.jpa.hibernate.ddl-auto=none

The default value for this property is create-drop. If not explicitly set to another value this can result in data being deleted that has been inserted during DB initialization via SQL scripts.

It turns out that I had a C++-style comment at the top of the DML file:

// convention: "string-based" keys are always fully lowercase!

If one removes this comment, the first INSERT is picked by the Spring Boot application initialization, otheriwse it is dropped.

One may note that // comments are sort-of allowed in H2:

H2 comments in SQL

> Compatibility-only non-standard syntax is marked in red, don't use it unless you need it for compatibility with other databases or old versions of H2.

In fact, replacing the // by /* ... */ ("bracketed comment") fixes the problem.

It is possible that running the DML script from the IDE strips the comment before passing it the H2 driver, which would explain why it works in that case.

Feels vaguely buggy. But where do I complain? Spring? H2?

This is h2-2.2.220 btw.