0

I have just started learning SQL and while trying to create my own table, I hit into a snag as I could not find out where exactly is wrong.

Pardon me if I have posted in the wrong place for such queries but I am in a lost as I am totally clueless where things have gone wrong...

I am getting the following issues in my SQL Developer (The server that I am using derived from a school server connected using an ip address but I am able to get its version - Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production, not sure if that helps)

ORA-00905: missing keyword
00905. 00000 -  "missing keyword"

in which the word "update" in the MYWORK table is underlined in red.

My code statements are as follow:

CREATE TABLE MYARTIST
(
    MyArtistID Int NOT NULL,
    LastName Char(25) NOT NULL,
    FirstName Char(25) NOT NULL,
    CONSTRAINT MyArtistPK PRIMARY KEY(MyArtistID)
);

-- This block is giving me issues, with errors stemming from the FOREIGN KEY portion 
CREATE TABLE MYWORK
(
    MyWorkID Int NOT NULL,
    Title Char(25) NOT NULL,
    Copy Char(25) NOT NULL,
    MyArtistID Int NOT NULL,
    CONSTRAINT MyWorkPK PRIMARY KEY(MyWorkID),
    CONSTRAINT MyArtistFK FOREIGN KEY(MyArtistID) REFERENCES MYARTIST(MyArtistID)
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
);

CREATE TABLE MYARTISTWORK
(
    MyArtistID INT NOT NULL,
    MyWorkID INT NOT NULL,
    CONSTRAINT MyArtWorkPK PRIMARY KEY(MyArtistID, MyWorkID),
    CONSTRAINT ArtFK FOREIGN KEY(MyArtistID) REFERENCES MYARTIST(MyArtistID)
        ON UPDATE NO ACTION
        ON DELETE CASCADE,
    CONSTRAINT WorkFK FOREIGN KEY(MyWorkID) REFERENCES MYWORK(MyWorkID)
        ON UPDATE NO ACTION
        ON DELETE CASCADE
);

If I remove the ON UPDATE... ON DELETE... statements, the table is created properly but not so, if I included in the statements. Any insights?

P.S - The code statements I have used follows in a similar fashion to a book I am reading - Database Processing: Fundamentals, Design, and Implementation, Global Edition

j.hall
  • 61
  • 5

1 Answers1

1

The answer is simple - in Oracle, there's no ON UPDATE clause so - remove it. You can leave ON DELETE (if it does something; if it is NO ACTION, you can omit it).

Littlefoot
  • 131,892
  • 15
  • 35
  • 57
  • I wasn't aware of that and I had presume that it will work (as the lab exercises did hint to follow the example in the book) – j.hall Nov 03 '20 at 07:40
  • Right; Oracle doesn't support that clause, unlike e.g. MySQL or PostgreSQL. – Littlefoot Nov 03 '20 at 07:50
  • Okay... Had presume it is universal as my lecture notes also did mention of `ON UPDATE xxx` Then I may need to find some other ways as I may have a table that requires the use of `ON UPDATE CASCADE`... – j.hall Nov 03 '20 at 08:10