由于尝试创建表的外键约束导致的SQL异常

I was trying to create a set of tables using the following script:

我试图使用以下脚本创建一组表：

DROP TABLE ORDERS IF EXISTS;
DROP TABLE INVOICE IF EXISTS;
CREATE TABLE ORDERS(
    ORDER_ID BIGINT NOT NULL PRIMARY KEY IDENTITY,
    ...
    INVOICE_ID BIGINT,
    ...
    );

CREATE TABLE INVOICE(
    INVOICE_ID BIGINT NOT NULL PRIMARY KEY IDENTITY,
    ...
    ORDER_ID BIGINT,
    ...
    FOREIGN KEY(ORDER_ID) REFERENCES ORDERS(ORDER_ID)
);
ALTER TABLE ORDERS ADD FOREIGN KEY(INVOICE_ID) REFERENCES INVOICE(INVOICE_ID);

This causes sql exception

这会导致sql异常

java.sql.SQLException: is referenced by FOREIGN KEY constraint: PUBLIC.INVOICE.SYS_FK_10079 in statement [ DROP TABLE ORDERS IF EXISTS]

java.sql.SQLException：由FOREIGN KEY约束引用：语句中的PUBLIC.INVOICE.SYS_FK_10079 [DROP TABLE ORDERS IF EXISTS]

I couldn't figure out how to solve this ..I need invoiceId in ORDERS table and OrderID in INVOICE table as foreign keys..That is why I used the ALTER TABLE statement..Still ,it seems wrong.. (btw I am using hsqldb)

我无法弄清楚如何解决这个问题。我需要在ORDERS表中的invoiceId和INVOICE表中的OrderID作为外键。这就是我使用ALTER TABLE语句的原因。但是，它似乎错了..（顺便说一句，我是使用hsqldb）

I would like to know how to do this properly..Any help most welcome..

我想知道如何正确地做到这一点..任何帮助最受欢迎..

1 个解决方案

#1

Drop the foreign key on orders first:

首先在订单上删除外键：

ALTER TABLE ORDERS DROP FOREIGN KEY(INVOICE_ID) REFERENCES INVOICE(INVOICE_ID);

Then run your commands.

然后运行您的命令。

#1