Home » SQL & PL/SQL » SQL & PL/SQL » Creating foreign key constraints for nested tables of user defined types oracle (Oracle )
Creating foreign key constraints for nested tables of user defined types oracle [message #663375] Thu, 01 June 2017 04:39 Go to next message
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

Re: Creating foreign key constraints for nested tables of user defined types oracle [message #663378 is a reply to message #663375] Thu, 01 June 2017 06:05 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
what am I doing wrong
You use nested table.
Stay with relational tables and model.
Anyway, Oracle will convert your model to a relational one with a cost of performances and code development and maintenance.
So directly do it as it should in a RDBMS: relational.

Also always post your Oracle version, with 4 decimals, as solution depends on it.

Re: Creating foreign key constraints for nested tables of user defined types oracle [message #663379 is a reply to message #663378] Thu, 01 June 2017 06:07 Go to previous messageGo to next message
Hatik
Messages: 12
Registered: June 2017
Junior Member
it is a transaction table, I am trying to use nested table to allow multiple data pass through single transaction, so that data would be dynamical. And I do not know how to do it in alternative way
Re: Creating foreign key constraints for nested tables of user defined types oracle [message #663380 is a reply to message #663379] Thu, 01 June 2017 06:12 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I don't know what you mean by "allow multiple data pass through single transaction" but there is no relation between number of data manipulations and transactions. You define when you start a transaction and when you end it and what work you will do between the two, one or many manipulations.

A nested table is just a detail table in a master/detail model.

Re: Creating foreign key constraints for nested tables of user defined types oracle [message #663383 is a reply to message #663380] Thu, 01 June 2017 06:34 Go to previous messageGo to next message
Hatik
Messages: 12
Registered: June 2017
Junior Member
I think I explained wrongly, when I say transaction I don't mean database transaction. This is a screen for already designed system, which has transactions(banking type transactions) so before the data is approved It should not be in the needed table, for this form when it is approved I am going through procedure to take out that data into the normal table where the data is separated as usual
hope this cleared things a little
Re: Creating foreign key constraints for nested tables of user defined types oracle [message #663384 is a reply to message #663383] Thu, 01 June 2017 07:03 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>what am I doing wrong
using nested tables
>and what might be the possible solution for it?
do NOT use nested tables
Re: Creating foreign key constraints for nested tables of user defined types oracle [message #663385 is a reply to message #663383] Thu, 01 June 2017 08:11 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

There is nothing in what you posted that shows that you need nested table.
You may need memory objects but not stored ones.

So in your application code you may use an object like:
CREATE OR REPLACE TYPE tx AS OBJECT(
  TX_NO NUMBER PRIMARY KEY NOT NULL,
  GROUPS groups_nt,
  GROUP_EMP group_emp_nt
);
/
although I wonder why you have 2 table types if they are not permanent.
If you explain your different types and their usage maybe we can help in a better design.

Re: Creating foreign key constraints for nested tables of user defined types oracle [message #663403 is a reply to message #663385] Thu, 01 June 2017 22:35 Go to previous message
Hatik
Messages: 12
Registered: June 2017
Junior Member
Thanks for your advises, It seems like using nested table makes the problems worse, I will try to find more orthodox way of handling things. I will first try, then If I have any more questions and can't solve them on my own, will ask again.
Previous Topic: Query Db table with Key Value
Next Topic: Passing value from one cursor to the other one.
Goto Forum:
  


Current Time: Fri Mar 29 09:28:55 CDT 2024