Posted in faq's-common doubts

why do we create self referential foreign keys – knowledge article for the new oracle dba

   February 10, 2011

 


We create self  referential foreign keys when we want to store hierarchical data in that table. A self referential foreign key column means that the column references itself like a snake swallowing its own tail.

Normally we self reference foreign keys in tables when we want to store data in hierarchical format.
 
 
   
   
   
    Oracle SQL: Storing hiearchical data
  One way to store hierarchical data is to create a self referencing table. It is called self referencing because the foreign key (column parent_id) references the same table as the table in which the foreign key is:

create table hierarchic_yahoo_tbl (

  id         number primary key,

  parent_id  references hierarchic_yahoo_tbl, — references itself

  descr      varchar2(20),

  active     number(1) not null check (active in (0,1))

);

This table is now filled with some values:

— 1st level:

insert into hierarchic_yahoo_tbl values ( 1, null, ‘Yahoo’         , 1);

— 2nd level:

insert into hierarchic_yahoo_tbl values ( 2,    1, ‘Entertainment’ , 1);

insert into hierarchic_yahoo_tbl values ( 3,    1, ‘Science’       , 1);

insert into hierarchic_yahoo_tbl values ( 4,    1, ‘Social Science’, 1);

— 3rd level (below Entertainment)

insert into hierarchic_yahoo_tbl values ( 5,    2, ‘Awards’        , 1);

insert into hierarchic_yahoo_tbl values ( 6,    2, ‘Comedy’        , 1);

insert into hierarchic_yahoo_tbl values ( 7,    2, ‘Humor’         , 0);

insert into hierarchic_yahoo_tbl values ( 8,    2, ‘Magic’         , 1);

— 4th level (below Humor)

insert into hierarchic_yahoo_tbl values (19,    7, ‘Jokes’         , 1);

insert into hierarchic_yahoo_tbl values (20,    7, ‘Advice’        , 1);

insert into hierarchic_yahoo_tbl values (21,    7, ‘Parody’        , 1);

— 4th level (below Magic)

insert into hierarchic_yahoo_tbl values (16,    8, ‘Card Tricks’   , 1);

insert into hierarchic_yahoo_tbl values (17,    8, ‘Magazines’     , 1);

insert into hierarchic_yahoo_tbl values (18,    8, ‘Organizations’ , 1);

— 3rd level (below Science)

insert into hierarchic_yahoo_tbl values ( 9,    3, ‘Astronomy’     , 1);

insert into hierarchic_yahoo_tbl values (10,    3, ‘Biology’       , 1);

insert into hierarchic_yahoo_tbl values (11,    3, ‘Geography’     , 1);

insert into hierarchic_yahoo_tbl values (12,    3, ‘Physics’       , 1);

insert into hierarchic_yahoo_tbl values (13,    3, ‘Research’      , 1);

— 4th level (below Astronomy)

insert into hierarchic_yahoo_tbl values (22,    9, ‘Galaxies’     , 1);

insert into hierarchic_yahoo_tbl values (23,    9, ‘Pictures’     , 1);

insert into hierarchic_yahoo_tbl values (24,    9, ‘Stars’        , 1);

— 4th level (below Physics)

insert into hierarchic_yahoo_tbl values (25,   12, ‘Electricity’  , 1);

insert into hierarchic_yahoo_tbl values (26,   12, ‘Mechanics’    , 1);

— 3rd level (below Social Science)

insert into hierarchic_yahoo_tbl values (14,    4, ‘Psychology’   , 1);

insert into hierarchic_yahoo_tbl values (15,    4, ‘Sociology’    , 1);

— 4th level (below Psychology)

insert into hierarchic_yahoo_tbl values (27,   14, ‘Conferences’  , 1);

insert into hierarchic_yahoo_tbl values (28,   14, ‘Intelligence’ , 1);

Showing the tree with start with .. connect by:

select

  lpad(‘ ‘, (level-1)*2, ‘ ‘) || descr

from

  hierarchic_yahoo_tbl

start with parent_id is null

connect by prior id = parent_id;

The next SQL statement restricts the output to subtrees whose active flag is set to 1. Additionally, it starts with the Entertainment sub tree (id = 2). The active flag of Humor is set to 0, so it won’t be displayed.
This technique is also covered in pruning in connect by

select

  lpad(‘ ‘, (level-1)*2, ‘ ‘) || descr

from

  hierarchic_yahoo_tbl

start with id = 2

connect by prior id = parent_id and active = 1;
           
 
 
Author: admin