• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Effect of setting primary key constraints on Nested Table

 
Ranch Hand
Posts: 155
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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!
 
Ranch Hand
Posts: 1143
1
Eclipse IDE Oracle Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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 ]
 
Don't get me started about those stupid light bulbs.
reply
    Bookmark Topic Watch Topic
  • New Topic