Home » SQL & PL/SQL » SQL & PL/SQL » Oracle Display parent, child, grandchild hierarchy (19.2)
| Oracle Display parent, child, grandchild hierarchy [message #685286] |
Sat, 04 December 2021 13:58  |
 |
Unclefool
Messages: 65
Registered: August 2021
|
Member |
|
|
I had a requirement to delete grandchildren rows, child rows and then a parent row from multiple tables. So I put together the following, which appears to work fine.
I suspect a dynamic mechanism would be better so the code could be shared in any database but that is out of my league as I'm not a developer.
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)
REFERENCES parent(id)
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);
DELETE from parent where value = 'a';
Secondly, Next to the child _name column I would like to add count(*) in the output but I'm unsure of the syntax to use in SQL other than SELECT count(*) from table_name.
Can someone point out my problem and tell me how to fix it and make the proper enhancements.
More importantly if there is a more efficient way to write this query I would appreciate the input as it appears the results are taking a while to materialize. Thanks in advance to all who answer and your expertise.
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
2 GRANDCHILD CHILD_GRANDCHILD_FK PARENT PARENT_PK
|
|
|
|
|
|
|
|
| Re: Oracle Display parent, child, grandchild hierarchy [message #685290 is a reply to message #685289] |
Sun, 05 December 2021 08:44  |
Solomon Yakobson
Messages: 3214
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
ON DELETE CASCADE is all you need:
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
);
SY.
|
|
|
|
|
|
Goto Forum:
Current Time: Sat Apr 01 06:27:24 CDT 2023
|
] 
Search
Help
Members
Register
Login
Home
