Spring Tool Suite 4中执行H2数据库SQL脚本时约束未找到错误排查

开发基于H2数据库的Web应用时，使用Spring Tool Suite 4执行schema.sql脚本的第6条语句（为Ingredient_Ref表添加外键关联Ingredient表的id字段）时，抛出org.h2.jdbc.JdbcSQLSyntaxErrorException异常，提示找不到"PRIMARY KEY | UNIQUE (ID)"约束。

错误信息 #

Failed to execute SQL script statement #6 of URL [file:/C:/Users/DELL/IdeaProjects/taco-cloud-1/bin/main/schema.sql]: alter table Ingredient_Ref add foreign key (ingredient) references Ingredient(id); nested exception is org.h2.jdbc.JdbcSQLSyntaxErrorException: Constraint "PRIMARY KEY | UNIQUE (ID)" not found; SQL statement:
alter table Ingredient_Ref add foreign key (ingredient) references Ingredient(id) [90057-214]

原SQL代码 #

create table if not exists Taco_Order (
    id identity,
    delivery_Name varchar(50) not null,
    delivery_Street varchar(50) not null,
    delivery_City varchar(50) not null,
    delivery_State varchar(2) not null,
    delivery_Zip varchar(10) not null,
    cc_number varchar(16) not null,
    cc_expiration varchar(5) not null,
    cc_cvv varchar(3) not null,
    placed_at timestamp not null
);

create table if not exists Taco (
    id identity,
    name varchar(50) not null,
    taco_order bigint not null,
    taco_order_key bigint not null,
    created_at timestamp not null
);

create table if not exists Ingredient_Ref (
    ingredient varchar(4) not null,
    taco bigint not null,
    taco_key bigint not null
);

create table if not exists Ingredient (
    id varchar(4) not null,
    name varchar(25) not null,
    type varchar(10) not null
);

alter table Taco
    add foreign key (taco_order) references Taco_Order(id);
alter table Ingredient_Ref
    add foreign key (ingredient) references Ingredient(id);

• 问题根源：H2数据库要求外键关联的被引用字段必须是主键或带有唯一约束，但Ingredient表仅给id字段添加了not null约束，未将其设为主键或唯一键，导致数据库无法找到符合要求的约束用于外键关联。
• 修复方法：修改Ingredient表的创建语句，将id字段设为主键，有两种方式：
  1. 创建表时直接声明主键：

     create table if not exists Ingredient (
         id varchar(4) not null primary key,
         name varchar(25) not null,
         type varchar(10) not null
     );
     

  2. 单独添加主键约束的ALTER语句：

     create table if not exists Ingredient (
         id varchar(4) not null,
         name varchar(25) not null,
         type varchar(10) not null
     );
     alter table Ingredient add primary key (id);
     

• 额外检查：Ingredient_Ref表的ingredient字段类型（varchar(4)）与Ingredient表的id字段类型一致，脚本执行顺序也先创建了Ingredient表再添加外键，这两部分无需调整。

内容的提问来源于stack exchange，提问作者Pascal Torti