FOREIGN KEY ON DELETE RESTRICT 错误 - Oracle

2022-01-20 00:00:00 foreign-keys oracle

最近我一直在尝试在表中添加以下外键，使用 Oracle 中的 RESTRICT 子句，使用以下命令:

Lately I have been trying to add the following foreign key in the table, with the RESTRICT Clause in Oracle with the following command.:

ALTER TABLE 
Employee_SalHead 
ADD CONSTRAINT PAYROLL_SHEAD_FKEY FOREIGN KEY
(
  SalHead_ID 
)
REFERENCES SalHead
(
  SalHead_ID
)
ON DELETE RESTRICT ENABLE;

这给了我以下错误:

从第 11 行开始的错误命令 - ALTER TABLE Employee_SalHead ADD约束 PAYROLL_SHEAD_FKEY 外键 ( SalHead_ID )REFERENCES SalHead ( SalHead_ID ) ON DELETE RESTRICT ENABLE错误报告 - SQL 错误:ORA-00905:缺少关键字00905. 00000 - 缺少关键字"*原因:
*行动:

Error starting at line : 11 in command - ALTER TABLE Employee_SalHead ADD CONSTRAINT PAYROLL_SHEAD_FKEY FOREIGN KEY ( SalHead_ID ) REFERENCES SalHead ( SalHead_ID ) ON DELETE RESTRICT ENABLE Error report - SQL Error: ORA-00905: missing keyword 00905. 00000 - "missing keyword" *Cause:
*Action:

此外，如果我通过 Oracle SQL 开发人员尝试相同的操作，我只会得到选项 Set Null、Cascade 和 No Action Only.

Also if I try the same through Oracle SQL developer, I get only the options Set Null, Cascade and No Action Only.

推荐答案

Oracle只支持ON DELETE SET NULL和ON DELETE CASCADE.您只需执行以下查询即可满足您的要求.不用提ON DELETE RESTRICT

Oracle only supports ON DELETE SET NULL and ON DELETE CASCADE. You can achieve your requirement by simply doing the below query. No need to mention ON DELETE RESTRICT

ALTER TABLE Employee_SalHead 
      ADD CONSTRAINT PAYROLL_SHEAD_FKEY FOREIGN KEY(SalHead_ID)
      REFERENCES SalHead(SalHead_ID);

ON DELETE NO ACTION 是默认设置.来自文档

无操作(默认)选项指定如果结果数据违反参照完整性约束，则无法更新或删除引用的键值.例如，如果一个主键值被外键中的值引用，那么由于依赖数据，被引用的主键值不能被删除.

The No Action (default) option specifies that referenced key values cannot be updated or deleted if the resulting data would violate a referential integrity constraint. For example, if a primary key value is referenced by a value in the foreign key, then the referenced primary key value cannot be deleted because of the dependent data.

相关文章