Win a copy of Mesos in Action this week in the Cloud/Virtualizaton forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Effect of setting primary key constraints on Nested Table

 
Brahim Bakayoko
Ranch Hand
Posts: 155
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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!
 
Avi Abrami
Ranch Hand
Posts: 1141
1
Java Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 155
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 ]
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic