Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2007
    Posts
    37

    Question Unanswered: On delete no action/restrict

    hi, i am just the beginner with oracle 10g, so plz don't laugh out loud to see my tiny tables...
    Code:
    CREATE TABLE stud(
    	id	varchar(10) NOT NULL,
    	name	varchar(10),
    	PRIMARY KEY(id)
    );
    
    CREATE TABLE address(
    	id	varchar(10),
    	address	varchar(10) NOT NULL,
    	PRIMARY KEY(address),
    	FOREIGN KEY(id) REFERENCES stud
    	ON DELETE NO ACTION
    );
    the output of it is ORA-00905: missing keyword
    the same output appears if i make it ON DELETE RESTRICT.

    i know ON DELETE NO ACTION is the default. and also i found that oracle 9i and higher supports these twos. ON DELETE CASCADE is doing fine. what's wrong?

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    hi, where exactly did you found that?
    Oracle 9.0.1,
    Oracle 9.2 and
    Oracle 10g documentation knows only ON DELETE CASCADE and ON DELETE SET NULL.

    If you do not specify ON DELETE part at all, you will not be able to delete stud if it exists in any address row (exception is thrown). I think it matches required RESTRICT behaviour. I have no idea what NO ACTION should do.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by flyboy
    I have no idea what NO ACTION should do.
    it means the database should take no action

    it is the equivalent of RESTRICT

    cannot off the top of my head remember which databases support which one, but NO ACTION is part of the SQL-92 standard
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •