deleting co-deoendent tables

View All Public Scripts Login to Run Script

Script Name deleting co-deoendent tables
Visibility Unlisted - anyone with the share link can access
Description "Peer" tables that have a master and auxiliary tables in 1-1 relationships. Master must have 1 and exactly one auxiliary table. When auxiliary table row is deleted, master row must also be deleted.
Area SQL General
Contributor Don Simpson
Created Friday May 01, 2020

Statement 1

create table MY_CENTRAL_TABLE ( 
    PK number(19) primary key,  
    DESCRIPTION varchar2(50 char) 
    )

Statement 2

create table MY_AUXILIARY_TABLE_1 ( 
    AUXILIARY_KEY number(19) primary key,  
    DESCRIPTION_1 varchar2(50 char),  
    foreign key (AUXILIARY_KEY) references MY_CENTRAL_TABLE (PK)  
        on delete cascade 
    )

Statement 3

create or replace trigger MY_AUXILIARY_TABLE_1_TD before delete on MY_AUXILIARY_TABLE_1 for each row 
begin 
    delete from MY_CENTRAL_TABLE where PK = :old.AUXILIARY_KEY; 
--exception  
--    when others then  
--        if SQLCODE = -4091 then  
--            DBMS_OUTPUT.put_line (SQLERRM); 
--        end if; 
end;

Statement 4

create table MY_AUXILIARY_TABLE_2 ( 
    AUXILIARY_KEY number(19) primary key,  
    DESCRIPTION_2 varchar2(50 char),  
    foreign key (AUXILIARY_KEY) references MY_CENTRAL_TABLE (PK)  
        on delete cascade 
    )

Statement 5

create or replace trigger MY_AUXILIARY_TABLE_2_TD before delete on MY_AUXILIARY_TABLE_2 for each row 
begin 
    delete from MY_CENTRAL_TABLE where PK = :old.AUXILIARY_KEY; 
--exception  
--    when others then  
--        if SQLCODE = -4091 then  
--            DBMS_OUTPUT.put_line (SQLERRM); 
--        end if; 
end;

Statement 6

create table MY_SECONDARY_AUXILIARY_TABLE_11 ( 
    SECONDARY_AUXILIARY_KEY number(19) primary key,  
    AUXILIARY_KEY number(19),  
    DESCRIPTION_1 varchar2(50 char),  
    foreign key (AUXILIARY_KEY) references MY_AUXILIARY_TABLE_1 (AUXILIARY_KEY)  
        on delete cascade 
    )

Statement 7

create table MY_SECONDARY_AUXILIARY_TABLE_12 ( 
    SECONDARY_AUXILIARY_KEY number(19) primary key,  
    AUXILIARY_KEY number(19),  
    DESCRIPTION_2 varchar2(50 char),  
    foreign key (AUXILIARY_KEY) references MY_AUXILIARY_TABLE_2 (AUXILIARY_KEY)  
        on delete cascade 
    )

Statement 8

insert into MY_CENTRAL_TABLE  
    values (1, 'MY_CENTRAL_TABLE row 1')

Statement 9

insert into MY_CENTRAL_TABLE  
    values (2, 'MY_CENTRAL_TABLE row 2')

Statement 10

insert into MY_AUXILIARY_TABLE_1  
    values (1, 'MY_AUXILIARY_TABLE_1 row 1')

Statement 11

insert into MY_AUXILIARY_TABLE_2  
    values (2, 'MY_AUXILIARY_TABLE_2 row 1')

Statement 12

insert into MY_SECONDARY_AUXILIARY_TABLE_11  
    values (101, 1, 'MY_SECONDARY_AUXILIARY_TABLE_11 row 1')

Statement 13

insert into MY_SECONDARY_AUXILIARY_TABLE_11  
    values (102, 1, 'MY_SECONDARY_AUXILIARY_TABLE_11 row 2')

Statement 14

insert into MY_SECONDARY_AUXILIARY_TABLE_12  
    values (101, 2,'MY_SECONDARY_AUXILIARY_TABLE_12 row 1')

Statement 15

insert into MY_SECONDARY_AUXILIARY_TABLE_12  
    values (102, 2, 'MY_SECONDARY_AUXILIARY_TABLE_12 row 2')

Statement 16
```
commit
```

Statement 17

select * from (select count(*) as T0 from MY_CENTRAL_TABLE) 
cross join (select count(*) as T01 from MY_AUXILIARY_TABLE_1) 
cross join (select count(*) as T11 from MY_SECONDARY_AUXILIARY_TABLE_11) 
cross join (select count(*) as T02 from MY_AUXILIARY_TABLE_2) 
cross join (select count(*) as T12 from MY_SECONDARY_AUXILIARY_TABLE_12)

Statement 18
```
delete MY_CENTRAL_TABLE
```
Statement 19
```
commit
```

Statement 20

select * from (select count(*) as T0 from MY_CENTRAL_TABLE) 
cross join (select count(*) as T01 from MY_AUXILIARY_TABLE_1) 
cross join (select count(*) as T11 from MY_SECONDARY_AUXILIARY_TABLE_11) 
cross join (select count(*) as T02 from MY_AUXILIARY_TABLE_2) 
cross join (select count(*) as T12 from MY_SECONDARY_AUXILIARY_TABLE_12)

Statement 21

insert into MY_CENTRAL_TABLE  
    values (1, 'MY_CENTRAL_TABLE row 1')

Statement 22

insert into MY_CENTRAL_TABLE  
    values (2, 'MY_CENTRAL_TABLE row 2')

Statement 23

insert into MY_AUXILIARY_TABLE_1  
    values (1, 'MY_AUXILIARY_TABLE_1 row 1')

Statement 24

insert into MY_AUXILIARY_TABLE_2  
    values (2, 'MY_AUXILIARY_TABLE_2 row 1')

Statement 25

insert into MY_SECONDARY_AUXILIARY_TABLE_11  
    values (101, 1, 'MY_SECONDARY_AUXILIARY_TABLE_11 row 1')

Statement 26

insert into MY_SECONDARY_AUXILIARY_TABLE_11  
    values (102, 1, 'MY_SECONDARY_AUXILIARY_TABLE_11 row 2')

Statement 27

insert into MY_SECONDARY_AUXILIARY_TABLE_12  
    values (101, 2,'MY_SECONDARY_AUXILIARY_TABLE_12 row 1')

Statement 28

insert into MY_SECONDARY_AUXILIARY_TABLE_12  
    values (102, 2, 'MY_SECONDARY_AUXILIARY_TABLE_12 row 2')

Statement 29
```
commit
```

Statement 30

select * from (select count(*) as T0 from MY_CENTRAL_TABLE) 
cross join (select count(*) as T01 from MY_AUXILIARY_TABLE_1) 
cross join (select count(*) as T11 from MY_SECONDARY_AUXILIARY_TABLE_11) 
cross join (select count(*) as T02 from MY_AUXILIARY_TABLE_2) 
cross join (select count(*) as T12 from MY_SECONDARY_AUXILIARY_TABLE_12)

Statement 31
```
delete MY_AUXILIARY_TABLE_1
```
Statement 32
```
commit
```
Statement 33
```
delete MY_AUXILIARY_TABLE_2
```
Statement 34
```
commit
```

Statement 35

select * from (select count(*) as T0 from MY_CENTRAL_TABLE) 
cross join (select count(*) as T01 from MY_AUXILIARY_TABLE_1) 
cross join (select count(*) as T11 from MY_SECONDARY_AUXILIARY_TABLE_11) 
cross join (select count(*) as T02 from MY_AUXILIARY_TABLE_2) 
cross join (select count(*) as T12 from MY_SECONDARY_AUXILIARY_TABLE_12)

Statement 36

drop table MY_SECONDARY_AUXILIARY_TABLE_11

Statement 37

drop table MY_SECONDARY_AUXILIARY_TABLE_12

Statement 38
```
drop table MY_AUXILIARY_TABLE_1
```
Statement 39
```
drop table MY_AUXILIARY_TABLE_2
```
Statement 40
```
drop table MY_CENTRAL_TABLE
```

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database