Referring a nested table in a relational insert query

Question

Below are the object types I have. Basically I have a person table and a child table as a nested table of person table.

I have a School table with a M:N Relationship with child table (nested). So I'm creating a intermediate table to insert child_school data.

How can I create that intermediate table and insert data?

create type school_t as object(
    sid number(5,2),
    name varchar(20))
/

create type child_t as object(
    cid number(5,2),
    name varchar(20))
/

create type childtable_t as table of child_t
/

create type person_t as object(
    pid number(5,2),
    name varchar(20),
    child childtable_t)
/

create table person_tab of person_t(
    pid primary key
)nested table child store as child_table
/

create table school_tab of school_t
/

--there's some problem. Below does not work.

create type school_child_t as object(
    cid ref person_t,
    sid ref school_t)
/

create table school_child_tab of school_child_t(
    cid references person_tab,
    sid references school_tab
)
/

--Here's what I want to do

create table school_child_tab(
    cid number(5,2) references childtable_t,
    sid number(5,2) references school_tab
)
/

cid reference should be the cid in nested table. The problem is referring it.

Answer 1

I saw your edit, and I was about to tell you it is impossible to reference a nested table externally.

The nested table is physically created as a distinct table that holds data separately from the parent table:

SQL> SELECT object_name, object_type
  2    FROM all_objects
  3   WHERE created > trunc(sysdate)
  4     AND object_type = 'TABLE';

OBJECT_NAME                    OBJECT_TYPE
------------------------------ -------------------
SCHOOL_TAB                     TABLE
CHILD_TABLE                    TABLE
PERSON_TAB                     TABLE

Here you can see that Oracle has created a CHILD_TABLE table, however it is hidden from us and can only be worked internally by Oracle:

SQL> select * from child_table;

ORA-22812: cannot reference nested table column's storage table

In this case I was pretty sure that you couldn't reference the child table in any way, however to my surprise this seems to work (we can't select from CHILD_TABLE, however we can reference to it):

SQL> alter table child_table add constraint pk_child_table primary key (cid);

Table altered

SQL> CREATE TABLE school_child_tab (
  2     cid REFERENCES child_table,
  3     sid REFERENCES school_tab
  4  );

Table created

You could build your inserts like this (I don't really like to store to store data as objects, but here you go):

SQL> insert into school_tab values (school_t(1, 'school A'));

1 row inserted

SQL> insert into person_tab values (
  2      person_t(1, 'person A', childtable_t(child_t(1, 'child A'))));

1 row inserted

SQL> insert into school_child_tab values (1, 1);

1 row inserted