aspose file tools*
The moose likes JDBC and the fly likes Effect of setting primary key constraints on Nested Table Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Effect of setting primary key constraints on Nested Table" Watch "Effect of setting primary key constraints on Nested Table" New topic
Author

Effect of setting primary key constraints on Nested Table

Brahim Bakayoko
Ranch Hand

Joined: Aug 29, 2003
Posts: 155
I have a question about setting primary key constraints on Nested Tables.
Let say I have a table:
CREATE TABLE A(a Integer, b nested_b) NESTED TABLE b STORE AS b_ntbl;
Will setting a primary key constraint on nested_b affect every row in table A or will it affect each row separately?
In order word, can an object with primary key K inserted in table A.b row 1 be inserted again in table A.b row 2?
Thanks!


SCJP, SCWCD, SCBCD, IBM CSD WebSphere v5, <br />A+, MCP 2000 and 2000 server, CST, and few incompleted certification tracks.<br /> <br />Ivory Coast<br /> <br />Analyze your web Request/Response @ <a href="http://webtools.servehttp.com" target="_blank" rel="nofollow">http://webtools.servehttp.com</a> down for a while...
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1134

Brahim,
I saw no mention in your post as to what database you are using.
Since I am only familiar with nested tables in an Oracle database, my answer will be biased.
Hopefully, either you are also using Oracle, or the following will also be applicable to the database you are using.
In Oracle, a nested table is really a separate table with a foreign key constraint to the nesting table. Each Oracle database table has an extra column called ROWID. This ROWID column acts as the foreign key for the nested table.
Since the nested table is no different to a regular table, defining a primary key constraint on the nested table will give you exactly the same result as defining a primary key on a regular table. Having said that, I'm not sure whether you can define a primary key constraint on a nested table (in Oracle), anyway. So perhaps your question is moot (as regards Oracle)?
Good Luck,
Avi.
[ April 28, 2004: Message edited by: Avi Abrami ]
Brahim Bakayoko
Ranch Hand

Joined: Aug 29, 2003
Posts: 155
Thanks Avi!

I forgot to provide an update for the topic.
I am using Oracle 9i.

The primary key constraint on a nested table is set at parent table definition and should include the nested_table_id.
Also each parent row is independ, which is what I wanted to know.


[ May 14, 2004: Message edited by: Brahim Bakayoko ]
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: Effect of setting primary key constraints on Nested Table