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 Go to next message
Unclefool
Messages: 85
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';
In addition, I took a shot at a query that will print out the hierarchy. This is where I'm having an issue. In the p_key column I was expecting to see the values child_pk and grandchild_pk but I'm seeing parent_pk.

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

Report message to a moderator

Re: Oracle Display parent, child, grandchild hierarchy [message #685287 is a reply to message #685286] Sat, 04 December 2021 15:18 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
Senior Member
I see no parent --> child --> grandchild. FK in both tables reference paprent, so you have parent --> child and parent --> grandchild.

SY.

Report message to a moderator

Oracle Display parent, child, grandchild hierarchy [message #685289 is a reply to message #685286] Sun, 05 December 2021 07:18 Go to previous messageGo to next message
Unclefool
Messages: 85
Registered: August 2021
Member
@SY Thanks for responding and pointing this out. If you don't mind me asking how I can change my test CASE to setup a parent->child-> grandchild relationship while still keeping the functionality I posted above. Where if a parent is deleted it will do the CASCADE and delete the child and grandchild.

I suspect the delete would have to first delete the grandchild, then the child then the parent.

Report message to a moderator

Re: Oracle Display parent, child, grandchild hierarchy [message #685290 is a reply to message #685289] Sun, 05 December 2021 08:44 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3273
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
);
So now when we delete from parent Oracle will check if there are tables with FKs referencing parent and will find table child. Then it will check one-by-one if to be deleted table parent row has children in table child. If not it will delete that table parent row. If there are children if will check FK action and since that FK has ON DELETE CASCADE Oracle will try to delete that table child row. At that point it will (same as with table parent) check if there are tables with FKs referencing table child and will find table granchild. Same way it will check one-by-one if to be deleted table child row has children in table grandchild. If not it will delete that table child row. If there are children if will check FK action and since that FK has ON DELETE CASCADE Oracle will try to delete that table grandchild row. Since table granchild has no child tables Oracle will delete corresponding rows in tables grandchild, child and then parent.

SY.

Report message to a moderator

Oracle Display parent, child, grandchild hierarchy [message #685291 is a reply to message #685286] Sun, 05 December 2021 11:05 Go to previous message
Unclefool
Messages: 85
Registered: August 2021
Member
@SY Thanks for your help, expertise, patience and detailed explanation. Now that the setup is correct my query mysteriously (sarcasm) works.

Report message to a moderator

Previous Topic: to_date in where clause
Next Topic: Using Procedure Update Date day -1
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Apr 19 12:23:06 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.