FOREIGN KEY ON DELETE RESTRICT Error - Oracle

oracle foreign-keys

11,074

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.

11,074

Author by

JavaDragon

Java Developer, Army Brat, Finding Myself ...... Extensive Gamer, Passionate for CODE and Long Rides! #SOreadytohelp

Updated on June 05, 2022

Comments

JavaDragon almost 2 years
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.
- JavaDragon almost 9 years
  
  tried, and didn't worked out , same error!!!
- Arun Palanisamy almost 9 years
  
  u r using mysql or oracle? & which version?
- JavaDragon almost 9 years
  
  oracle 11g and the tool i have mentioned above is Oracle SQL Developer 4.0.3.16
pete almost 9 years

This answer doesn't address the posted question since the constraint does not actually do what Adempiere_HotCake attempted to do. DELETE RESTRICT would prevent deleting a row in SalHead if a row in Employee_SalHead referenced the row via the foreign key.In Oracle (at least 11G or older), this can only be implemented with a trigger.
Arun Palanisamy almost 9 years

I suppose foreign key is enough to prevent the same. FYR SqlFiddle. Try deleting any row in salhead , you will get the error
pete almost 9 years

Your fiddle plays nicely :). I retract my statement! Thanks!