Creating foreign key constraints for nested tables of user defined types oracle [message #663375] |
Thu, 01 June 2017 04:39 |
|
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
|
|
|
|
|
|
|
|
|
|