CODEWITHSUNDEEP
sqldatabaseoracle-databaseoracle-sqldeveloper
Sqizh
Sqizh

Reputation: 1

SQL Error: ORA-00905: missing keyword with ON DELETE NO ACTION Foreign Key

Not sure what i'm missing to get this error? 

  Error starting at line : 95 in command -
    ALTER TABLE Official_Language ADD CONSTRAINT Language_Official_Language_fk
    FOREIGN KEY (language_code)
    REFERENCES Language (language_code)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    NOT DEFERRABLE
    Error report -
    SQL Error: ORA-00905: missing keyword
    00905. 00000 -  "missing keyword"
    *Cause:    
    *Action:

Any help is appreciated :-) Thanks

Upvotes: 0

Views: 4895

Answers (3)

StuartLC
StuartLC

Reputation: 107247

Unlike MySql, Oracle doesn't support ON DELETE NO ACTION - the options are:

  • (Nothing) (i.e. enforce referential integrity)
  • ON DELETE CASCADE (i.e. cascading delete)
  • ON DELETE SET NULL (update keys to null, if permitted by the column nullability)

Reference

SqlFiddle here

Upvotes: 0

Multisync
Multisync

Reputation: 8797

ALTER TABLE Official_Language ADD CONSTRAINT Language_Official_Language_fk
FOREIGN KEY (language_code)
REFERENCES Language (language_code)
NOT DEFERRABLE;

Possible choices are ON DELETE [CASCADE | SET NULL] By default (if you omit ON DELETE) it is the same as you have written.

NOT DEFERRABLE is default (you can also omit it)

Upvotes: 1

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59456

This one should work:

ALTER TABLE Official_Language ADD CONSTRAINT Language_Official_Language_fk
    FOREIGN KEY (language_code)
    REFERENCES Language (language_code)
    NOT DEFERRABLE;

Default is "no action", Oracle does not make any difference on delete/update.

Possible keywords are ON DELETE CASCADE or ON DELETE SET NULL

Upvotes: 0

Related Questions