0

I'm trying to add a primary and foreign key to a nested table, struggling to know how.

This is what I have;

create or replace type profile as object 
(
id VARCHAR2(10), --- Suppose to be Primary Key
userID VARCHAR2(10) --- Suppose to be Foreign Key for user table
);

create or replace type profile_nest as table of profile;

CREATE OR REPLACE TYPE user_t UNDER group_T
(profile profile_nest_ty,);


CREATE TABLE user OF user_t
(id NOT NULL,
PRIMARY KEY (id), 
nested table profile store as profile_storage_tbl;

Now the problem is this part, trying to do a foreign key -

alter table profile_storage_tbl add CONSTRAINT fk_userID FOREIGN KEY (userID)
REFERENCES user(id);

Gives this error -

*Error starting at line 3 in command:
alter table profile_storage_tbl add CONSTRAINT fk_userID FOREIGN KEY (userID) REFERENCES user(id)
Error report:
SQL Error: 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.

APC
  • 144,005
  • 19
  • 170
  • 281
user1165419
  • 663
  • 2
  • 10
  • 21

3 Answers3

1

Either you create 2 separate tables profile_storage_tbl and user with a foreign key between them or you create profile_storage_tbl as a nested table within the user table. It doesn't make sense to try to do both. (In fact nested tables make little sense to me, period - but that's another matter!)

Tony Andrews
  • 129,880
  • 21
  • 220
  • 259
  • Hello, thanks for the reply. I have created profile_storage_tbl as a nested table in user table. The issue is, I have another table that I will need to have a relation with the profile_storage_tbl nested table. So I need to know how I'm going to add constraints. – user1165419 Jan 26 '12 at 14:43
1

It is just as the exception text says, creating a foreign key constraint on nested table columns is not allowed (Oracle 11).

There is sort of a workaround described here: http://ksun-oracle.blogspot.com/2011/05/foreign-key-on-nested-table.html. But there is no guarantee, that this would work on the next oracle release.

Juergen Hartelt
  • 664
  • 4
  • 5
  • Thanks. I wanted to ask, can nested tables have primary keys or unique identifiers for each row? – user1165419 Jan 26 '12 at 14:49
  • Yes to primary key constraint and unique constraint. The latter you can see in the linked article. You can see an example for a primary key here:http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2318607631616#4040376500346333254 – Juergen Hartelt Jan 26 '12 at 20:33
0

Behind the scene oracle will create two tables profile_storage_tbl and user whereas profile_storage_tbl has a foreign key on user. You can do that on your own, with the advatage to have better control over the releations (also to other tables).

Benjamin
  • 139
  • 6