I have a bunch of tables, which are joined by Foriegn keys. Based on those Foriegn keys if I delete a parent record all the children, grandchild….rows will be deleted. My setup works fine.
Is there a way to determine the number of rows deleted from each table with a query without coding the SELECT statement before and after the delete like I did in my test CASE below.
I tried using this but it only returns 1 for the parent row that was deleted.
begin
DELETE from parent where value = 'a';
dbms_output.put_line('count = '|| sql%rowcount);
end;
I'm looking for an output similar to this:
Rows Deleted
parent 1
child 3
grandchild
Below is my setup and test case.
create table parent (
id NUMBER(10),
value varchar2(30),
constraint parent_pk primary key (id)
);
CREATE TABLE child
( id NUMBER(10) not null,
value NUMBER(10) not null,
constraint child_pk primary key (id,value),
CONSTRAINT parent_child_fk
FOREIGN KEY (id)
REFERENCES parent(id)
ON DELETE CASCADE
);
CREATE TABLE grandchild
( id NUMBER(10) not null,
value NUMBER(10) not null,
constraint grandchild_pk primary key (id,value),
CONSTRAINT child_grandchild_fk
FOREIGN KEY (id,value)
REFERENCES child(id,value)
ON DELETE CASCADE
);
insert into parent values (1,'a');
insert into parent values (2,'b');
insert into parent values (3,'c');
insert into child values (1,1);
insert into child values (1,2);
insert into child values (1,3);
insert into child values (2,1);
insert into child values (2,2);
insert into child values (2,3);
insert into child values (3,1);
insert into child values (3,2);
insert into child values (3,3);
insert into grandchild values (1,1);
insert into grandchild values (1,2);
insert into grandchild values (1,3);
insert into grandchild values (2,1);
insert into grandchild values (2,2);
insert into grandchild values (2,3);
insert into grandchild values (3,1);
insert into grandchild values (3,2);
insert into grandchild values (3,3);
SELECT (
SELECT COUNT(*)
FROM parent
) AS parent_cnt,
(
SELECT COUNT(*)
FROM child
) AS child_cnt,
(
SELECT COUNT(*)
FROM grandchild
) AS grandchild_cnt
FROM dual
DELETE from parent where value = 'a';
PARENT_CNT CHILD_CNT GRANDCHILD_CNT
3 9 9
SELECT (
SELECT COUNT(*)
FROM parent
) AS parent_cnt,
(
SELECT COUNT(*)
FROM child
) AS child_cnt,
(
SELECT COUNT(*)
FROM grandchild
) AS grandchild_cnt
FROM dual
PARENT_CNT CHILD_CNT GRANDCHILD_CNT
2 6 6
Here is a query that shows the relationships between tables, which I found. Can this query be expanded to help achieve my goal?
with f as (
select constraint_name, table_name, r_constraint_name
from user_constraints
where constraint_type = 'R'
),
p as (
select constraint_name, table_name
from user_constraints
where constraint_type = 'P'
),
j (child_table, f_key, parent_table, p_key) as (
select f.table_name, f.constraint_name, p.table_name, f.r_constraint_name
from p join f on p.constraint_name = f.r_constraint_name
union all
select 'PARENT', (select constraint_name from p where table_name = 'PARENT'), null, null from dual
)
select level as lvl, j.*
from j
start with parent_table is null
connect by nocycle parent_table = prior child_table
order by lvl, parent_table, child_table;
LVL CHILD_TABLE F_KEY PARENT_TABLE P_KEY
1 PARENT PARENT_PK - -
2 CHILD PARENT_CHILD_FK PARENT PARENT_PK
3 GRANDCHILD CHILD_GRANDCHILD_FK CHILD CHILD_PK
Happy holidays and thanks in advance to all who answer.
