Error when adding foreign key constraint

Question

I am trying to alter a table in Oracle's SQL*Plus I was able to create a table using the reserved word order using quotes. I was also able to add a primary key constraint. When it comes to adding the foreign key I have this error. I researched about what I could be doing wrong, but I can't find any satisfactory answer. I'll appreciate any help. THanks!

SQL> ALTER TABLE "ORDER"
  2  ADD CONSTRAINT ORDER_FK
  3  FOREIGN KEY(C_NO) 
  4  REFERENCES CUSTOMER(C_NO)
  5  ON DELETE RESTRICT;
ON DELETE RESTRICT
          *
ERROR at line 5:
ORA-00905: missing keyword

You might consider adopting a convention of plural names for tables, not only because they generally hold multiple items but because you are very much less likely to clash with a reserved word. docs.oracle.com/cd/B28359_01/appdev.111/b31231/appb.htm — David Aldridge
– David Aldridge, Commented May 19, 2013 at 6:23

Marc Shapiro · Accepted Answer · 2013-05-19 01:41:34Z

4

Oracle does not support "RESTRICT"

According to Oracle the options are: (1) omitting the ON DELETE (2) ON DELETE CASCADE and (3) ON DELETE SET NULL.

I believe that omitting the ON DELETE is closest to ON DELETE RESTRICT.

answered May 19, 2013 at 1:41

Marc Shapiro

5712 silver badges2 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Steve Wellens Over a year ago

If you just want to prevent the delete...from the referenced link: "The foreign key definitions in both variations of this statement omit the ON DELETE clause, causing Oracle to prevent the deletion of a department if any employee works in that department."

Collectives™ on Stack Overflow

Error when adding foreign key constraint

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related