Creating foreign key constraints for nested tables of user defined types oracle [message #663375] |
Thu, 01 June 2017 04:39 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/03a1d0dbaad8738d1aa5b180a6cd3cdf?s=64&d=mm&r=g) |
Hatik
Messages: 12 Registered: June 2017
|
Junior Member |
|
|
I want to create table with 3 columns TX_NO(Primary Key), GROUPS which is a nested table of group_type user defined type, GROUP_EMP which is also a nested table of group_emp_type user defined type, here are the declaration for group_type and nested table:
CREATE OR REPLACE TYPE group_type AS OBJECT(
NAME VARCHAR2(100),
PARENT_GROUP_ID NUMBER
);
CREATE OR REPLACE TYPE groups_nt IS TABLE OF group_type;
declaration of group_emp_type:
CREATE OR REPLACE TYPE group_emp_type AS OBJECT(
EMP_CODE VARCHAR2(100),
GROUP_ID NUMBER
);
CREATE OR REPLACE TYPE group_emp_nt IS TABLE OF group_emp_type;
and creation of the table :
CREATE TABLE TX(
TX_NO NUMBER PRIMARY KEY NOT NULL,
GROUPS groups_nt,
GROUP_EMP group_emp_nt,
CONSTRAINT PARENT_GROUP_FK FOREIGN KEY(GROUPS.PARENT_GROUP_ID) REFERENCES GROUPS(ID),
CONSTRAINT GEMP_GROUP_FK FOREIGN KEY(GROUP_EMP.GROUP_ID) REFERENCES GROUPS(ID),
CONSTRAINT GEMP_EMP_FK FOREIGN KEY(GROUP_EMP.EMP_CODE) REFERENCES USERS(CODE)
)NESTED TABLE GROUPS STORE AS stor_GROUPS_1, NESTED TABLE GROUP_EMP STORE AS stor_GROUP_EMP_2;
I am trying to set the foreign keys for the nested tables which is unsuccessful - I get ORA-02337: not an object type column, what am I doing wrong and what might be the possible solution for it?
[Updated on: Thu, 01 June 2017 05:26] Report message to a moderator
|
|
|
|
|
|
|
|
|
|