I am trying to make foreign key connections between flat and nested tables in SQL Developer.
Scenario One:
Flat table to Nested table using a foreign key connection.
In this scenario, I want to connect the flat table "GRANPARENT_TABLE" directly with the table "CHILD_TABLE", which "CHILD_TABLE" is nested to "PARENT_TABLE".
First I create the nested table "CHILD_TABLE" INSIDE the table "PARENT_TABLE"
CREATE TYPE CHILD_OBJECT AS OBJECT(
CHILD_ID NUMBER
);
CREATE TYPE CHILD_TYPE IS TABLE OF CHILD_OBJECT;
CREATE TABLE PARENT_TABLE (
PARENT_ID NUMBER,
CHILD CHILD_TYPE
)NESTED TABLE CHILD STORE AS CHILD_TABLE;
After creating the tables "PARENT_TABLE" and "CHILD_TABLE", I declare a primary key to the nested table "CHILD_TABLE"
ALTER TABLE CHILD_TABLE
ADD CONSTRAINT CHILD_TABLE_PK PRIMARY KEY (CHILD_ID);
Finally, I create the "GRANDPARENT_TABLE" and at the same time I make the foreign key connection between the flat table "GRANDPARENT_TABLE" and the nested table "CHILD_TABLE".
CREATE TABLE GRANDPARENT_TABLE(
GRANDPARENT_ID NUMBER,
CHILD_ID NUMBER,
CONSTRAINT GRANDPARENT_TO_CHILD FOREIGN KEY (CHILD_ID) REFERENCES CHILD_TABLE(CHILD_ID)
);
The result of the above scenario is the successful creation of the foreign key connection, as shown below:
Flat table to Nested table foreign key connection
So far, so good.
Scenario Two:
Nested table to Flat table using a foreign key connection.
In this scenario, I want to connect the nested table "CHILD_TABLE", which is inside the table "PARENT_TABLE", directly with the flat table "GRANDPARENT_TABLE".
First I create the flat table "GRANDPARENT_TABLE"
CREATE TABLE GRANDPARENT_TABLE(
GRANDPARENT_ID NUMBER,
CHILD_ID NUMBER
);
Then create the the nested table "CHILD_TABLE" inside the table "PARENT_TABLE"
CREATE TYPE CHILD_OBJECT AS OBJECT(
CHILD_ID NUMBER
);
CREATE TYPE CHILD_TYPE IS TABLE OF CHILD_OBJECT;
CREATE TABLE PARENT_TABLE (
PARENT_ID NUMBER,
CHILD_ID CHILD_TYPE
)NESTED TABLE CHILD_ID STORE AS CHILD_TABLE;
I continue declaring the primary key of the nested table "CHILD_TABLE"
ALTER TABLE CHILD_TABLE
ADD CONSTRAINT CHILD_TABLE_PK PRIMARY KEY (CHILD_ID);
Finally, I TRY to make the foreign key connection between the flat table "GRANDPARENT_TABLE" and the nested table "CHILD_TABLE"
ALTER TABLE CHILD_TABLE
ADD CONSTRAINT CHILD_TO_GRANDPARENT FOREIGN KEY (CHILD_ID) REFERENCES GRANDPARENT_TABLE(CHILD_ID);
but, in this scenario, Oracle SQL Developer won't allow such action and return the following error:
Error report -
ORA-30730: referential constraint not allowed on nested table column
30730. 00000 - "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.
Although, in the first scenario, Oracle SQL Developer would let me make the connection between the two tables, in the second one, it won't allow such action. Why is this happening? Is there any way to make the connection possible in the second scenario?