Home » SQL & PL/SQL » SQL & PL/SQL » Non key-preserved table, insertion through view (DB(Oracle 10.2.0.1.0) OS(RHEL 2.6.9-22.EL))
Non key-preserved table, insertion through view [message #402300] Fri, 08 May 2009 10:09 Go to next message
prajjwal
Messages: 28
Registered: January 2008
Location: kolkata
Junior Member

Please look at the following test case. I created a primary key (aa) for table t1 and not for t2. This allows me to insert into table t2 through view v12. My understanding, t2 is a non key-preserved table. How does this insertion happen after all?



USR1@orclxxx > create table t1(aa int, bb int, cc int);

Table created.

USR1@orclxxx > create table t2(aa int, dd int);

Table created.

USR1@orclxxx > create view v12 as select t1.aa t1aa, t1.bb t1bb, t1.cc t1cc, t2.aa t2aa, t2.dd t2dd from t1,t2 where t1.aa=t2.aa;

View created.

USR1@orclxxx > insert into t1 values (1,11,111);

1 row created.

USR1@orclxxx > insert into t1 values (2,22,222);

1 row created.

USR1@orclxxx > insert into t2 values (2,222);

1 row created.

USR1@orclxxx > commit;

Commit complete.

USR1@orclxxx > select * from v12;

T1AA T1BB T1CC T2AA T2DD
---------- ---------- ---------- ---------- ----------
2 22 222 2 222


USR1@orclxxx > select * from user_updatable_columns where TABLE_NAME='V12';

OWNER TABLE_NAME COLUMN_NAME UPD INS DEL
------------------------------ ------------------------------ ------------------------------ --- --- ---
USR1 V12 T1AA NO NO NO
USR1 V12 T1BB NO NO NO
USR1 V12 T1CC NO NO NO
USR1 V12 T2AA NO NO NO
USR1 V12 T2DD NO NO NO



USR1@orclxxx > ALTER TABLE T1 ADD PRIMARY KEY (AA);

Table altered.


USR1@orclxxx > ALTER VIEW V12 COMPILE;

View altered.

USR1@orclxxx > select * from user_updatable_columns where TABLE_NAME='V12';

OWNER TABLE_NAME COLUMN_NAME UPD INS DEL
------------------------------ ------------------------------ ------------------------------ --- --- ---
USR1 V12 T1AA NO NO NO
USR1 V12 T1BB NO NO NO
USR1 V12 T1CC NO NO NO
USR1 V12 T2AA YES YES YES
USR1 V12 T2DD YES YES YES


USR1@orclxxx > INSERT INTO V12(T2AA,T2DD) VALUES (123,456);

1 row created.

Re: Non key-preserved table, insertion through view [message #402390 is a reply to message #402300] Sat, 09 May 2009 16:06 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
what did you think should happen?

It inserts a row into t2. The insert supplied values for these two columns. Based on the tables in the view, there is no ambiguity about what you mean for this insert.

Maybe try an update and see what happens. I generally only see errors related to key preservation when using the MERGE statement.

In any event, this exmaple demonstrates the pitfalls of updating via views anyway. I'd suggest that if you want to enforce any specific rules for insert/update/delete against a view, use an instead of trigger to dictate them.

Additionally, try using the WITH CHECK OPTION though this does not really address your root concern I think.

Good luck, Kevin

create table t1(aa int, bb int, cc int);
create table t2(aa int, dd int);
create view v12 as select t1.aa t1aa, t1.bb t1bb, t1.cc t1cc, t2.aa t2aa, t2.dd t2dd from t1,t2 where t1.aa=t2.aa;
ALTER TABLE T1 ADD PRIMARY KEY (AA);
ALTER VIEW V12 COMPILE;
INSERT INTO V12(T2AA,T2DD) VALUES (123,456);
select * from t1;
select * from t2;


drop view v12;
delete from t2;
commit;
create view v12 as select t1.aa t1aa, t1.bb t1bb, t1.cc t1cc, t2.aa t2aa, t2.dd t2dd from t1,t2 where t1.aa=t2.aa
with check option;

INSERT INTO V12(T2AA,T2DD) VALUES (123,456);
Re: Non key-preserved table, insertion through view [message #402438 is a reply to message #402390] Sun, 10 May 2009 07:47 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
What is non-key-preserved about it? You join T2 to T1 on T1.aa, and you have a PK on T1.aa, which ensures that it contributes no more than one row to the join for each row in T2.

It all looks as expected to me. Perhaps you could explain why you think it is non-key-preserved.

Ross Leishman
Re: Non key-preserved table, insertion through view [message #402504 is a reply to message #402438] Mon, 11 May 2009 03:30 Go to previous messageGo to next message
prajjwal
Messages: 28
Registered: January 2008
Location: kolkata
Junior Member

Very correctly said Ross. If we stick to the definition of key-preserved tables, T2 indeed is a key preserved table.

Even then I have a doubt. If T2 is key preserved, why not T1?

---------------------

USR1@orclxxx > insert into v12(T1AA,T1BB,T1CC) values (9,99,999);
insert into v12(T1AA,T1BB,T1CC) values (9,99,999)
*
ERROR at line 1:
ORA-01779: cannot modify a column which maps to a non key-preserved table
Re: Non key-preserved table, insertion through view [message #402507 is a reply to message #402390] Mon, 11 May 2009 03:36 Go to previous messageGo to next message
prajjwal
Messages: 28
Registered: January 2008
Location: kolkata
Junior Member

Thanks Kevin for your suggestions, "with check option" and "instead of trigger"!!
However my question was why this pitfall? Smile
Re: Non key-preserved table, insertion through view [message #402530 is a reply to message #402507] Mon, 11 May 2009 06:40 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
The error is related to the join affects between T2 and T1.

If you add the following primary ke to t2, your insert succeeds:

alter table t2 add primary key (aa);

I can only go back to a simple definition of KEY PRESERVED:

Quote:
If row can only be seen once in a view, then it is key preserved.

After placing the above primary key on T2, it is guaranteed that a row can be seen only once for table T1 through your view. Thus the insert can be allowed. I presume that if T2 was a parent to T1 by some foreign key from T1 back to T2, then again T1 would be key preserved and the insert allowed.

I suggest that your insert to T2 was allowed because the insert referenced only columns from T2 and because Oracle understands that through this view, rows from T2 will not be duplicated as a result of the join to T1 and thus it is OK to do the insert to T2 because even though there is no "KEY" on T2, the intent of "key perservation" has still been met for T2, which is, that rows from T2 will only show up once in the view.

I laymans terms, this is what KEY PRESERVED means to me, not that the key is actually in the view, but that inspite of (or because of) the joins in the view, for some given table in the view, rows are guaranteed to be seen only once.

Understanding the above, the behavior you are seeing is expected and correct.

Forget about primary keys, unique keys, and foreign keys as keys, think instead of these as "INFORMATION" and think about ROWID as the key. The question then becomes this:

Using the information provided by constraints on tables, and joins in the view, are the ROWIDs from some particular table going to be seen more than once or not? (eg. will the view retrive the same row from some table more than once or not?).

If the same ROWID from a given table can be seen more than once through the view, then this table is NOT key preserved in the view.

If however it is guaranteed that a ROWID from a given table can only be seen once through the view then this table is key preserved within this view.

How do you know? Use the definition of constraints on the tables and joins in the view to figure it out.

With this understanding, in your scenario, T1 is not key preserved but T2 is. This is because the join to T2 could cause rows from T1 to be "duplicated" using your view, but rows from T2 can never be duplicated in your view.

For example, if this were the data:

t1(1,1,1);
t2(1,2);
t2(1,3);

Then how many times does the row from T1 show up in your view? A: twice (once for each row in T2), thus T1 is not key preserved in your view.

However, each row from T2 can only show up once in your view thus T2 is key preserved in your view.

How do we know?, because the contraints on the tables referenced by our view along with the joins in the view between these tables, gives us enough information to know each of the above facts.

Does this help at all? Kevin

[Updated on: Mon, 11 May 2009 07:08]

Report message to a moderator

Re: Non key-preserved table, insertion through view [message #402697 is a reply to message #402530] Tue, 12 May 2009 02:04 Go to previous message
prajjwal
Messages: 28
Registered: January 2008
Location: kolkata
Junior Member

This really helps Smile ..Very thankful to you Kevin for your followup. Infact this helps to clear some other confusions as well.
Previous Topic: Receiving Email Messages through Oracle procedure.
Next Topic: Get a Procedure Code
Goto Forum:
  


Current Time: Thu Dec 08 08:51:26 CST 2016

Total time taken to generate the page: 0.09241 seconds