HSQLDB.创建表后添加外键时出错

Question

创建后，我需要在表中添加一个外键。

我使用此请求：ALTER TABLE lessons ADD FOREIGN KEY (id_teacher) REFERENCES teachers (id);

执行后，我收到错误：java.sql.SQLSyntaxErrorException: user lacks privilege or object not found: PUBLIC.LESSONS

我该如何解决它？

发送查询：Statement statement = connection.createStatement(); statement.execute(request);

整个查询：

CREATE TABLE lessons (
id int IDENTITY, 
lesson_name varchar(50) NOT NULL, 
id_teacher int NOT NULL,
id_group int NOT NULL
);
CREATE TABLE teachers (
id int IDENTITY,
teacher_name varchar(50) NOT NULL,
id_lesson int NOT NULL,
id_group int NOT NULL
);

CREATE TABLE groups (
id int IDENTITY,
group_name varchar(50) NOT NULL,
id_lesson int NOT NULL,
id_curator int NOT NULL
);
ALTER TABLE lessons 
ADD FOREIGN KEY (id_teacher) REFERENCES teachers (id) 
ADD FOREIGN KEY (id_group) REFERENCES groups (id);
ALTER TABLE teachers 
ADD FOREIGN KEY (id_lesson) REFERENCES lessons (id) 
ADD FOREIGN KEY (id_group) REFERENCES groups (id);
ALTER TABLE groups 
ADD FOREIGN KEY (id_lesson) REFERENCES lessons (id) 
ADD FOREIGN KEY (id_curator) REFERENCES teachers (id);

Answer 1

返回错误是因为您将整个脚本作为一个语句一起发送。它在执行之前被编译。编译 ALTER TABLE 语句时，尚未创建该表。

分别执行每个语句。同样如前所述，每个外键需要一个 ALTER TABLE 语句。