Re: How to create tables referenced each other

"Brian Peasland" <oracle_dba_at_remove_spam.peasland.com> wrote in message news:3F0300BF.5009A707_at_remove_spam.peasland.com...
> The ON DELETE SET NULL and ON DELETE RESTRICT clauses are not valid for
> Oracle. And it would really help if you would run each statement and
> post any and all error messages you are receiving.
>

Brian,

A minor correction:
As tested on oracle 9.2.0.3: on delete set null is *valid*. Though "on delete restrict" is still invalid.

Here is an example:
SQL> create table t1 (a number, b varchar2(3), constraint a_pk primary key (a));
Table created.

SQL> create table t2 (a number, b varchar2(6), constraint a_fk foreign key (a) references t1 on delete set null);
Table created.

SQL> insert into t1 (a, b) values (1,'abc'); 1 row created.

SQL> insert into t1 (a, b) values (2,'xyz'); 1 row created.

SQL> insert into t2 (a,b) values (1,'abc'); 1 row created.

SQL> select * from t1;

         A B
---------- ---

         1 abc
         2 xyz

SQL> select * from t2;
         A B
---------- ------
         1 abc

SQL> delete from t1 where a = 1;
1 row deleted.

SQL> select * from t1;

         A B
---------- ---

         2 xyz

SQL> select * from t2;

         A B
---------- ------

           abc

Anurag Received on Wed Jul 02 2003 - 11:26:42 CDT