Wednesday, May 4, 2011

Foreign Key on Nested Table

This Blog will make an attempt to define a referential constraint on a nested table column.

At first I copy and run the code from Book: Expert Oracle Database Architecture: 9i and 10g (http://www.apress.com/9781590595305). 

drop table dept_and_emp cascade constraints;
drop type emp_tab_type;
drop type emp_type;

create or replace NONEDITIONABLE type emp_type  -- NONEDITIONABLE since 11.2
--create or replace type emp_type
as object
(empno       number(4),
 ename       varchar2(10),
 job         varchar2(9),
 mgr         number(4),
 hiredate    date,
 sal         number(7, 2),
 comm        number(7, 2)
)
/

create or replace NONEDITIONABLE type emp_tab_type  -- NONEDITIONABLE since 11.2
--create or replace type emp_tab_type
as table of emp_type
/

create table dept_and_emp
(deptno    number(2) primary key,
 dname     varchar2(14),
 loc       varchar2(13),
 emps      emp_tab_type
)
nested table emps store as emps_nt
/

alter table emps_nt add constraint
emps_empno_unique unique(empno)
/

insert into dept_and_emp values(1, 'Dept_1', 'Loc_1',
  new emp_tab_type(new emp_type(11, 'Emp_11', 'Job_11', null, date'1962-01-11', 1, 1),
                   new emp_type(12, 'Emp_12', 'Job_12', 11, date'1962-01-12', 2, 2)
                   ));
                  
insert into dept_and_emp values(2, 'Dept_2', 'Loc_2',
  new emp_tab_type(new emp_type(21, 'Emp_21', 'Job_21', null, date'1962-02-11', 1, 1),
                   new emp_type(22, 'Emp_22', 'Job_22', 21, date'1962-02-12', 2, 2)
                   )); 
                  
commit;
As already showed in the book, if run:

alter table emps_nt add constraint mgr_fk foreign key(mgr) references emps_nt(empno);

one will get an error:

ORA-30730: referential constraint not allowed on nested table column

Now I will try with other columns. At first, I list the columns from master and nested table (not relevant columns are removed):

SQL > select o.object_name tname, c.name, c.type# ctype
        from   dba_objects o, sys.col$ c
       where c.obj# = o.object_id
         and o.object_name in ('EMPS_NT', 'DEPT_AND_EMP')
       order by o.object_name, c.name;

DEPT_AND_EMP            SYS_NC0000400005$    23
EMPS_NT                 NESTED_TABLE_ID      23

I see both DEPT_AND_EMP.SYS_NC0000400005$ and EMPS_NT.NESTED_TABLE_ID having the same type: 23(SQLT_BIN).
   
then run:

alter table emps_nt add constraint emps_nt_fk foreign key(nested_table_id)
references dept_and_emp(sys_nc0000400005$);

it will create a foreign key constraint EMPS_NT_FK.

I can verify it by:

SQL > select constraint_name, constraint_type, table_name 
        from dba_constraints where table_name = 'EMPS_NT';

EMPS_NT_FK          R     EMPS_NT
EMPS_EMPNO_UNIQUE   U     EMPS_NT

Tested on Oracle 10gr2 and 11gr2, both works.

Now I am lost with Oracle documentation:

ORA-30730: referential constraint not allowed on nested table column
    Cause: An attempt was made to define a referential constraint on a nested table column.
    Action: Do not specify referential constraints on nested table columns.

By the way, on 11gr2, with the query:

SQL > select  index_name, table_name, column_name 
        from  dba_ind_columns 
       where table_name in ('EMPS_NT', 'DEPT_AND_EMP');

SYS_C0016818               DEPT_AND_EMP      DEPTNO
SYS_C0016819               DEPT_AND_EMP      EMPS
SYS_FK0000184243N00004$    EMPS_NT           NESTED_TABLE_ID
EMPS_EMPNO_UNIQUE          EMPS_NT           EMPNO

we can see the column NESTED_TABLE_ID in EMPS_NT is indexed by: SYS_FK0000184243N00004$. So Oracle follows its rule: "foreign keys should be indexed" (Oracle® Database Concepts 11g Release 2 (11.2) Part Number E16508-05 in http://download.oracle.com/docs/cd/E11882_01/server.112/e16508/datainte.htm#CNCPT88886)

However, this index can not be found in 10gr2.    

We can also verify this foreign key constraint:

SQL > delete dept_and_emp where deptno = 1;
ORA-02292: integrity constraint (K.EMPS_NT_FK) violated - child record found

Now disabling this constraint, delete deptno 1 again:

SQL > alter table emps_nt disable constraint emps_nt_fk;
SQL > delete dept_and_emp where deptno = 1;
1 row deleted.

We can see that both parent and child rows are deleted:

SQL > select SYS_NC0000400005$, t.* from dept_and_emp t;

SQL > select /*+ nested_table_get_refs */ NESTED_TABLE_ID, SYS_NC_ROWINFO$, t.* from emps_nt t;

(Without hint: nested_table_get_refs, it hits: "ORA-22812: cannot reference nested table column's storage table")

However, if we delete with two additional hints:

SQL > delete /*+ no_index(t SYS_FK0000184243N00004$) nested_table_get_refs */ dept_and_emp t
      where deptno = 2;
1 row deleted.

We can see that only parent row is deleted, but child rows remain:

SQL > select SYS_NC0000400005$, t.* from dept_and_emp t;
no rows selected

SQL > select /*+ nested_table_get_refs */ NESTED_TABLE_ID, SYS_NC_ROWINFO$, t.* from emps_nt t;

E40DC694BEAC31C7E04478E7D1E92954 (21; Emp_21; Job_21; ; 11.02.1962; 1; 1) 21 Emp_21 Job_21  11.02.1962 1 1

E40DC694BEAC31C7E04478E7D1E92954 (22; Emp_22; Job_22; 21; 12.02.1962; 2; 2) 22 Emp_22 Job_22 21 12.02.1962 2 2

2 rows selected.

Taking the NESTED_TABLE_ID of above query, we can delete these child rows directly:

SQL > delete /*+ nested_table_get_refs */ emps_nt t
      where nested_table_id=hextoraw('E40DC694BEAC31C7E04478E7D1E92954');
2 rows deleted.

SQL > select /*+ nested_table_get_refs */ NESTED_TABLE_ID, SYS_NC_ROWINFO$, t.* from emps_nt t;
no rows selected

 
at