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

huangapple go评论107阅读模式
英文:

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

问题

I will provide translations for the non-code parts of your text:

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]

这是实际的错误:

执行SQL脚本语句#6时出错,URL为[file:/C:/Users/DELL/IdeaProjects/taco-cloud-1/bin/main/schema.sql]:alter table Ingredient_Ref add foreign key (ingredient) references Ingredient(id); 嵌套异常是org.h2.jdbc.JdbcSQLSyntaxErrorException:未找到约束“PRIMARY KEY | UNIQUE (ID)”;SQL语句为: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?

这是我的SQL代码。请问是否有语法错误,或者我漏掉了什么?

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

它旨在与其他代码(HTML、Java)一起运行,作为从这个H2数据库获取数据的Web应用程序。

英文:

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.

答案1

得分: 0

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.

英文:

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.

huangapple
  • 本文由 发表于 2023年7月24日 19:46:07
  • 转载请务必保留本文链接:https://go.coder-hub.com/76754163.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定