Re: Delete records which has referance to the same able

Hi,

One idea would be an ON DELETE CASCADE constraint on the table. See below for a simple test case...

SQL> create table t0828(c number, d number);

Table created.

SQL> alter table t0828 add constraint t0828_pk primary key(c);

Table altered.

SQL> alter table t0828 add constraint t0828_fk foreign key(d) references t0828(c);

Table altered.

SQL> insert into t0828 values(1,1);

1 row created.

SQL> insert into t0828 values(2,1);

1 row created.

SQL> insert into t0828 values(3,null);

1 row created.

SQL> insert into t0828 values(4,3);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t0828;

         C D
---------- ----------

         2          1
         3
         4          3
         1          1

SQL> delete from t0828 where c = 3;
delete from t0828 where c = 3
*
ERROR at line 1:
ORA-02292: integrity constraint (REP.T0828_FK) violated - child record found

SQL> alter table t0828 drop constraint t0828_fk;

Table altered.

SQL> alter table t0828 add constraint t0828_fk foreign key(d) references t0828(c) on delete cascade;

Table altered.

SQL> delete from t0828 where c = 3;

1 row deleted.

SQL> commit;

Commit complete.

SQL> select * from t0828;

         C D
---------- ----------

         1          1
         2          1

SQL> After we add the constraint with the clause noted above, we can delete with no extra SQL.

Regards,

Steve Received on Mon Aug 28 2006 - 08:19:46 CDT