Constraint "PRIMARY KEY | UNIQUE (ID)" not found; in SQL statement using the Spring Tool Suite 4 IDE

Question

This is the actual error

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]

This is my sql code. Please is there any syntax error, or is there anything I'm missing?

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);

It's meant to run alongside other codes (html, java) as a web application that fetches these data from this h2 database.

Answer 1

You must add primary key (or unique) constraints to your tables before creation of referential constraints.

You also should not use identity as data type in modern versions of H2, this syntax is not supported and works only in few compatibility modes for a limited compatibility with historic versions of H2.

You need to replace id identity with id bigint generated by default as identity primary key in definitions of Taco_Order and Taco tables and you also need to replace id varchar(4) not null with id varchar(4) primary key in definitions of Ingredient_Ref and Ingredient tables.