Oracle 中的外键约束问题
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7996229/
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
Foreign Key Constraint Issue in Oracle
提问by elithrar
Having issues declaring a FK in Oracle 9i. I've looked at a number of examples here on SO and in some online docs (e.g. http://www.techonthenet.com/oracle/foreign_keys/foreign_delete.php) without any real luck; trying a similar syntax to that in the link generates the same error:
在 Oracle 9i 中声明 FK 时遇到问题。我在 SO 和一些在线文档（例如http://www.techonthenet.com/oracle/foreign_keys/foreign_delete.php）中查看了许多示例，但没有任何真正的运气；尝试与链接中的语法类似的语法会产生相同的错误：
Error at Command Line:19 Column:4
Error report:
SQL Error: ORA-02253: constraint specification not allowed here
02253. 00000 - "constraint specification not allowed here"
*Cause: Constraint specification is not allowed here in the statement.
*Action: Remove the constraint specification from the statement.
An excerpt of the SQL itself is as below. "Line 19" refers to the line starting with CONSTRAINT
SQL 本身的摘录如下。“第 19 行”是指以CONSTRAINT
CREATE TABLE Flight (
flight_no varchar2(10) NOT NULL,
airplane_id varchar2(20) NOT NULL
CONSTRAINT flight_airplane_id_fk FOREIGN KEY (airplane_id) REFERENCES Airplane (airplane_id)
ON UPDATE RESTRICT ON DELETE RESTRICT,
dept_date date NOT NULL,
...
Alternatively, trying it without the CONSTRAINTkeyword generates an error about a right parenthesis that I can't seem to see is missing.
或者，在没有CONSTRAINT关键字的情况下尝试它会生成一个关于右括号的错误，我似乎看不到它丢失了。
PS: I understand ON UPDATE RESTRICT is the default behaviour in Oracle, but I prefer to be explicit wherever possible.
PS：我知道 ON UPDATE RESTRICT 是 Oracle 中的默认行为，但我更喜欢尽可能明确。
回答by Justin Cave
First off, in Oracle, there is no ON UPDATE RESTRICTor ON DELETE RESTRICToption. Those appear to be valid in other database engines but they aren't present in the constraint syntax diagramand do not appear to be valid. There is an ON DELETEclause but the only two valid options are CASCADEor SET NULL. There is no ON UPDATEclause.
首先，在 Oracle 中，没有ON UPDATE RESTRICTorON DELETE RESTRICT选项。这些在其他数据库引擎中似乎有效，但它们不存在于约束语法图中，并且似乎无效。有一个ON DELETE子句，但仅有的两个有效选项是CASCADEor SET NULL。没有ON UPDATE条款。
If we add a comma at the end of the airplane_iddefinition before the constriant definition and remove the two invalid clauses, your DDL should be valid
如果我们airplane_id在约束定义之前的定义末尾添加一个逗号并删除两个无效子句，则您的 DDL 应该是有效的
CREATE TABLE Flight (
flight_no varchar2(10) NOT NULL,
airplane_id varchar2(20) NOT NULL,
CONSTRAINT flight_airplane_id_fk
FOREIGN KEY (airplane_id) REFERENCES Airplane (airplane_id),
dept_date date NOT NULL,
<<more columns>>
);
回答by Pablo Santa Cruz
Put your constraint at the end:
把你的约束放在最后：
CREATE TABLE Flight (
flight_no varchar2(10) NOT NULL,
airplane_id varchar2(20) NOT NULL,
dept_date date NOT NULL,
CONSTRAINT flight_airplane_id_fk FOREIGN KEY (airplane_id) REFERENCES Airplane (airplane_id) ON UPDATE RESTRICT ON DELETE RESTRICT
);
