Oracle8 and ON UPDATE

From: Michael J Pacione CS1998 <mpacione_at_cs.strath.ac.uk>
Date: Sun, 03 Dec 2000 12:11:39 +0000
Message-ID: <3A2A387B.6A5C1FEE@cs.strath.ac.uk>

I hope this is the correct newsgroup to post this to - is there a diference between comp.databses.oracle and comp.databases.oracle.misc? I must also confess to being a bit of a newbie when it comes to Oracle, SQL, etc.!

Does anyone know if the ON UPDATE clause for specifying foreign keys is supported in Oracle 8.1? This is a fragment of the SQL*Plus output generated when I try to implement my database:

SQL>
SQL> CREATE TABLE HOMEORDER

  2        (ORDERNUM DEC(6) NOT NULL CHECK (ORDERNUM>0),
  3         NAME VARCHAR(30) NOT NULL CHECK (LENGTH (REPLACE (TRANSLATE

(UPPER (NAME),'ABCDEFGHIJKLMNOPQRSTUVWXYZ ',

'XXXXXXXXXXXXXXXXXXXXXXXXXXX'), 'X', '')) = 0),
  4         CARDNUM DEC(16) NOT NULL CHECK (CARDNUM>0),
  5         COST DEC(4) NOT NULL CHECK (COST>0),
  6         HOUSENUM VARCHAR(4) NOT NULL CHECK (HOUSENUM>0),
  7         POSTCODE VARCHAR(7) NOT NULL,
  8         DELDATETIME DATE NOT NULL,
  9         STORENUM DEC(3) NOT NULL,
 10        PRIMARY KEY (ORDERNUM),
 11        FOREIGN KEY (STORENUM) REFERENCES STORE(STORENUM) ON UPDATE

CASCADE ON DELETE CASCADE );
FOREIGN KEY (STORENUM) REFERENCES STORE(STORENUM) ON UPDATE CASCADE ON DELETE CASCADE )

ERROR at line 11:
ORA-00905: missing keyword

I know the third line's a bit of a nightmare - it's just ensuring that the Name attribute only allows leters and spaces - if anyone has a better way, please feel free to suggest it! :)

Line 11 works fine with just the ON DELETE clause on its own, but I get the above error when I try to use ON UPDATE as well. Swapping the order of the ON... clauses gives a 'missing right parenthesis' error. I'm beginning to suspect that the ON UPDATE clause isn't actually supported in the version of Oracle I'm using.
The info is:

SQL*Plus Release 3.3.3.0.0 - Production
Oracle 8i Enterprise Edition Release 8.1.5.0.0 - Production With the Partitioning and Java options
PL/SQL Relase 8.1.5.0.0 - Production

There's no mention of ON UPDATE in the Oracle 8i docs at http://oradoc.photo.net/ora81/DOC/server.815/a67779/ch4e.htm#21922 - only of ON DELETE. Is ON UPDATE not supported in 8.1? If not, is there now an alternative way to implement its functionality?

Thanks a lot,

Michael Pacione

--
"Computer Science: A study akin to numerology and astrology, but lacking
the precision of the former and the success of the latter."
Stan Kelly-Bootle, The Devil's DP Dictionary, 1981.

Received on Sun Dec 03 2000 - 06:11:39 CST