CODEWITHSUNDEEP
oracle-databaseforeign-keys
JavaDragon
JavaDragon

Reputation: 451

FOREIGN KEY ON DELETE RESTRICT Error - Oracle

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.

Upvotes: 2

Views: 14030

Answers (1)

Arun Palanisamy
Arun Palanisamy

Reputation: 5459

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.

Upvotes: 8

Related Questions