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...
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 ]
Joined: Aug 29, 2003
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 ]