对于DELETE RESTRICT错误的FOREIGN KEY - Oracle [英] FOREIGN KEY ON DELETE RESTRICT Error - Oracle

问题描述

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

  ALTER TABLE 
 Employee_SalHead 
 ADD CONSTRAINT PAYROLL_SHEAD_FKEY FOREIGN KEY 
（
 SalHead_ID 
）
参考SalHead 
（
 SalHead_ID 
）
 ON DELETE RESTRICT ENABLE; 
  

这给了我以下错误：

错误从行开始：11在命令中 - ALTER TABLE Employee_SalHead ADD
CONSTRAINT PAYROLL_SHEAD_FKEY FOREIGN KEY（SalHead_ID）
参考SalHead（SalHead_ID）ON DELETE RESTRICT ENABLE
错误报告 - SQL错误：ORA-00905：缺少关键字
00905. 00000 - 缺少关键字
*原因：

*操作：

< blockquote>

另外，如果我通过Oracle SQL开发人员尝试相同，我只能得到Set Null，Cascade和No Action Only选项。

ON DELETE SET NULL 和 ON DELETE CASCADE 。您只需执行以下查询即可实现您的要求。无需提及 ON DELETE RESTRICT

  ALTER TABLE Employee_SalHead 
 ADD CONSTRAINT PAYROLL_SHEAD_FKEY FOREIGN KEY（SalHead_ID）
 REFERENCES SalHead（SalHead_ID）; 
  

删除任何操作为默认值。
从文档

无操作（默认）选项指定如果生成的数据违反参照完整性约束，则不能更新或删除引用的键值。例如，如果主键值由外键中的值引用，则由于相关数据，引用的主键值不能被删除。

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;

This gave me the following error:

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:

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

解决方案

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 is Default. From Documentation

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.

这篇关于对于DELETE RESTRICT错误的FOREIGN KEY - Oracle的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！