SQL 在 H2 数据库中定义外键约束
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/41515563/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Defining a Foreign key constraint in H2 Databases
提问by l3kov
I am new in coding so I made a tables in SQL server and it worked, so i used the same command in H2 and it said I have a syntax problems with the second table, someone can help?
我是编码新手，所以我在 SQL Server 中创建了一个表并且它起作用了，所以我在 H2 中使用了相同的命令，它说我的第二个表有语法问题，有人可以帮忙吗？
CREATE TABLE TOURISTINFO(
TOURISTINFO_ID INT PRIMARY KEY,
NAME VARCHAR(25) NOT NULL,
NATIONALITY VARCHAR(15) NOT NULL
)
CREATE TABLE PLANETICKETS(
DESTINATION VARCHAR(10) NOT NULL,
TICKETPRICE NUMERIC(8,2) NOT NULL,
TOURISTINFO_ID INT FOREIGN KEY REFERENCES TOURISTINFO
)
The error is
错误是
Syntax error in SQL statement "CREATE TABLE PLANETICKETS(
DESTINATION VARCHAR(10) NOT NULL,
TICKETPRICE NUMERIC(8,2) NOT NULL,
TOURISTINFO_ID INT FOREIGN[*] KEY REFERENCES TOURISTINFO
)"; expected "(, FOR, UNSIGNED, NOT, NULL, AS, DEFAULT, GENERATED, NOT, NULL, AUTO_INCREMENT, BIGSERIAL, SERIAL, IDENTITY, NULL_TO_DEFAULT, SEQUENCE, SELECTIVITY, COMMENT, CONSTRAINT, PRIMARY, UNIQUE, NOT, NULL, CHECK, REFERENCES, ,, )"; SQL statement:
CREATE TABLE PLANETICKETS(
DESTINATION VARCHAR(10) NOT NULL,
TICKETPRICE NUMERIC(8,2) NOT NULL,
TOURISTINFO_ID INT FOREIGN KEY REFERENCES TOURISTINFO
) [42001-173] 42001/42001
回答by David Brossard
Two-step process
两步过程
- Create the table without a foreign key
- 创建没有外键的表
CREATE TABLE PLANETICKETS(
DESTINATION VARCHAR(10) NOT NULL,
TICKETPRICE NUMERIC(8,2) NOT NULL,
TOURISTINFO_ID INT
)
- Add the foreign key constraint
- 添加外键约束
ALTER TABLE PLANETICKETS
ADD FOREIGN KEY (TOURISTINFO_ID)
REFERENCES TOURISTINFO(TOURISTINFO_ID)
One-step process
一步法
CREATE TABLE PLANETICKETS(
DESTINATION VARCHAR(10) NOT NULL,
TICKETPRICE NUMERIC(8,2) NOT NULL,
TOURISTINFO_ID INT,
foreign key (TOURISTINFO_ID) references touristinfo(TOURISTINFO_ID)
)
回答by Dazed
I would add one option to @david-brossard's answer:
我会在@david-brossard 的回答中添加一个选项：
CREATE TABLE PLANETICKETS(
DESTINATION VARCHAR(10) NOT NULL,
TICKETPRICE NUMERIC(8,2) NOT NULL,
TOURISTINFO_ID INT,
CONSTRAINT FK_PLANETICKET_TOURIST FOREIGN KEY (TOURISTINFO_ID) REFERENCES TOURISTINFO(TOURISTINFO_ID)
)
By using a Constaint Name Definitionthe foreign key is named explicitly, otherwise H2 assigns it a name based on it's own naming scheme e.g. CONSTRAINT_74.
通过使用Constaint Name Definition，外键被显式命名，否则 H2 会根据它自己的命名方案为其分配一个名称，例如 CONSTRAINT_74。
I feel this makes it safer to manage the constraint later by avoiding ambiguity on use of the name and referencing the name directly defined previously e.g.
我觉得这可以通过避免使用名称的歧义并引用先前直接定义的名称来更安全地管理约束，例如
ALTER TABLE PLANETICKETS DROP CONSTRAINT FK_PLANETICKET_TOURIST;
ALTER TABLE PLANETICKETS ADD CONSTRAINT FK_PLANETICKET_TOURIST FOREIGN KEY (TOURISTINFO_ID) REFERENCES TOURISTINFO(TOURISTINFO_ID) ON DELETE CASCADE;
I have started doing this as standard, based on my use of Flyway for an installable software product.
基于我将 Flyway 用于可安装的软件产品，我已经开始将此作为标准进行。
In theory the sequence of Flyway migrations should result in constraints (including Foreign Keys) being applied in the same order and therefore H2 should assign the same name in each copy of database. However, the worry point is removed if a direct name is assigned - one which is referenced in previous migration scripts - rather than one deduced from checking the assigned name in a single database instance.
理论上，Flyway 迁移的顺序应该导致约束（包括外键）以相同的顺序应用，因此 H2 应该在每个数据库副本中分配相同的名称。但是，如果分配了直接名称（在以前的迁移脚本中引用的名称），而不是通过检查单个数据库实例中的分配名称推断出的名称，则无需担心。
回答by danidemi
I would improve on @david-brossard's answer:
我会改进@david-brossard 的回答：
CREATE TABLE PLANETICKETS(
DESTINATION VARCHAR(10) NOT NULL,
TICKETPRICE NUMERIC(8,2) NOT NULL,
TOURISTINFO_ID INT,
FOREIGN KEY(TOURISTINFO_ID) REFERENCES TOURISTINFO -- no need for touristinfo(TOURISTINFO_ID)
)
When you define the FOREIGN KEYin this case you can omit to reference explicitly the TOURISTINFO_IDcolumn because H2 knows what column is the primary key in PLANETICKETS.
FOREIGN KEY在这种情况下定义 时，您可以省略显式引用该TOURISTINFO_ID列，因为 H2 知道哪个列是 中的主键PLANETICKETS。
