ON DELETE SET NULL shown as NO ACTION

From: yong huang <yong321_at_yahoo.com>
Date: Mon, 24 Jul 2000 07:54:37 -0700 (PDT)
Message-Id: <10568.112757@fatcity.com>

I find that the delete_rule of ON DELETE SET NULL is shown as NO ACTION in user_constraints in my database. ON DELETE CASCADE is shown correctly. Real NO ACTION is shown correctly too (although I hope it's shown differently so I can tell the difference). Can anyone confirm this to be a bug?

SQL> create table parent (a number primary key);

Table created.

SQL> create table child1 (a number, constraint fk_child1 foreign key (a) references parent on delete cascade);

Table created.

SQL> create table child2 (a number, constraint fk_child2 foreign key (a) references parent on delete set null);

Table created.

SQL> insert into parent values (1);

1 row created.

SQL> insert into parent values (2);

1 row created.

SQL> insert into child1 values (1);

1 row created.

SQL> insert into child2 values (2);

1 row created.

SQL> delete from parent where a = 1;

1 row deleted.

SQL> select * from child1;

no rows selected

SQL> delete from parent where a = 2;

1 row deleted.

SQL> select * from child2;

        A

---

SQL> select count(*) from child2 where a is null;

 COUNT(*)

---

        1

SQL> select table_name, delete_rule from user_constraints where table_name in ('CHILD1', 'CHILD2') and constraint_type = 'R';

TABLE_NAME                     DELETE_RU


------------------------------ ---------

CHILD1                         CASCADE
CHILD2                         NO ACTION

SQL> select * from v$version;

BANNER

---

Oracle8i Enterprise Edition Release 8.1.5.0.0 - Production PL/SQL Release 8.1.5.0.0 - Production
CORE Version 8.1.3.0.0 - Production
TNS for Solaris: Version 8.1.5.0.0 - Production NLSRTL Version 3.4.0.0.0 - Production

Thanks.

Yong Huang
yong321_at_yahoo.com

---

Do You Yahoo!?
Get Yahoo! Mail – Free email you can access from anywhere! Received on Mon Jul 24 2000 - 09:54:37 CDT