CODEWITHSUNDEEP
sqloracle-databasesql-view
Nocofoolme
Nocofoolme

Reputation: 91

Non-key-preserved table error for update with unique data

I am confused about key preserved join view update in Oracle (and in general); when doing an update using a select with a join, whether the key preserved property is expected for the table that is updated or for the embedded select statement.

In the below example:

SQL> create table tabX (x1 number, x2 number);

Table created.

SQL>  create table tabY (y1 number, y2 number);

Table created.

SQL> insert into tabx values (1,11);

1 row created.

SQL> insert into tabx values (2,12);

1 row created.

SQL> insert into taby values(1,21);

1 row created.

SQL>  insert into taby values(2,22);

1 row created.

SQL> commit;

Commit complete.

the output of select is unique but still the update fails. There is no ambiguity or duplication in both select and update - so why does it fail?

SQL> select x2,y2 from tabx,taby where tabx.x1=taby.y1;

        X2         Y2
---------- ----------
        11         21
        12         22

SQL> update (select x2,y2 from tabx,taby where tabx.x1=taby.y1) set x2=y2;
update (select x2,y2 from tabx,taby where tabx.x1=taby.y1) set x2=y2
                                                           *
ERROR at line 1:
ORA-01779: cannot modify a column which maps to a non key-preserved table

Upvotes: 0

Views: 112

Answers (2)

Alex Poole
Alex Poole

Reputation: 191285

You know there is no duplication, but when the statement is parsed the optimiser doesn't know there isn't, and more importantly that there can't be, any duplication. The parser looks at statistics to decide how to work but in general doesn't look at data. The update statement may be cached and reused, so even if it did look and saw there were no duplicates right now, the data might have changed when the same statement is run again later from the cache.

Imagine you added an extra row:

insert into taby values(2,222);

then your query gets:

select x2,y2 from tabx,taby where tabx.x1=taby.y1;

        X2         Y2
---------- ----------
        11         21
        12         22
        12        222

Now the update has two possible values to set both x2=12 values to; should they both be 22, or both 222, or one of each? Oracle has no way of knowing what is right and can't choose which of the first two options should be used (and certainly can't use the third).

Now, that isn't your actual situation, but you need to tell Oracle that the situation cannot occur. The wording of the error is hint here; for the view to preserve a key, there must be a key. If you define tabY with a primary or unique key:

create table tabY (y1 number primary key, y2 number);

insert into taby values(1,21);

insert into taby values(2,22);

then my made-up third insert isn't allowed, Oracle knows that, and can apply that knowledge to the update:

update (select x2,y2 from tabx,taby where tabx.x1=taby.y1) set x2=y2;

2 rows updated.

select x2,y2 from tabx,taby where tabx.x1=taby.y1;

        X2         Y2
---------- ----------
        21         21
        22         22

It doesn't matter how many rows you have with x1=2, you want them all to be updated to x2=22. So x1 doesn't need to be unique, and you don't need a UK/Pk on that. But y1 does have to be unique so you know which single value of y2 to use for all of those x1=2 rows.

What i don't understand is whether the view becomes key preserved based if the columns involved join are declared unique or the actual values that is getting updated.

It's the columns, not the values. But the bit I think I didn't explain well is that because you are doing set x2=y2 it can see that only the tabx table is actually being updated; so it needs to be able to identify which rows on the tabx table are being affected, and then it needs to work out for each of those rows which matching taby row to get the y2 values from - which it gets from the join condition.

For each x2 being updated it has to identify a single y2 value to use, which it does by looking for the taby row where taby.y1 is that row's tabx.x1. If there was - or just could be - multiple rows in taby which match that condition then it wouldn't know which of those options to use - 22 or 222 in my example. There has to be a single match in taby, so y1 has to be unique, and has to be declared as unique via a UK or PK.

There could be lots of different x2 values that get updated to the same y2 value; you could also have

insert into tabx values(2,13);
insert into tabx values(2,14);
etc.

and all of those would still be updated to the same value - 22 - because the non-unique x1 value in all of those rows still maps to a single ytab row with that tables UK/PK via the join condition.

Upvotes: 1

Flavian-Calinic Stuparu
Flavian-Calinic Stuparu

Reputation: 199

If you do not want to use a key, as AlexPoole suggested, you could easily turn it into a MERGE statement (this is something we try each time we encounter the "non-key-preserved" error when updating a view) :

MERGE 
 INTO tabx
USING taby
   ON (x1 = y1)
 WHEN MATCHED THEN
   UPDATE
      SET x2 = y2
;

Upvotes: 1

Related Questions