Oracle中的外键约束问题 [英] Foreign Key Constraint Issue in Oracle

问题描述

在Oracle 9i中声明FK时遇到问题.我在SO和一些在线文档中查看了许多示例(例如 http: //www.techonthenet.com/oracle/foreign_keys/foreign_delete.php )，没有任何真正的运气；尝试使用与链接中相似的语法会产生相同的错误:

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:

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.

SQL的摘录如下. 第19行"是指以CONSTRAINT

An excerpt of the SQL itself is as below. "Line 19" refers to the line starting with 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,
...

或者，尝试不使用CONSTRAINT关键字会生成有关我似乎看不到的右括号的错误.

Alternatively, trying it without the CONSTRAINT keyword generates an error about a right parenthesis that I can't seem to see is missing.

PS:我了解ON UPDATE RESTRICT是Oracle中的默认行为，但我希望尽可能地露骨.

PS: I understand ON UPDATE RESTRICT is the default behaviour in Oracle, but I prefer to be explicit wherever possible.

推荐答案

首先，在Oracle中，没有ON UPDATE RESTRICT或ON DELETE RESTRICT选项.这些在其他数据库引擎中似乎有效，但在约束语法图，似乎无效.有一个ON DELETE子句，但是仅有的两个有效选项是CASCADE或SET NULL.没有ON UPDATE子句.

First off, in Oracle, there is no ON UPDATE RESTRICT or ON DELETE RESTRICT option. Those appear to be valid in other database engines but they aren't present in the constraint syntax diagram and do not appear to be valid. There is an ON DELETE clause but the only two valid options are CASCADE or SET NULL. There is no ON UPDATE clause.

如果我们在约束定义之前的airplane_id定义末尾添加逗号并删除两个无效子句，则您的DDL应该有效

If we add a comma at the end of the airplane_id definition before the constriant definition and remove the two invalid clauses, your DDL should be valid

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>>
);

这篇关于Oracle中的外键约束问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！