Why isn't table A always key-preserved if equijoined to the key of table B?

Hi,

It seems to me table A should always be key-preserved if equijoined to the key of table B, but Oracle seems to disagree. Example:

SQL> create table jvd_t1 (c1 number, c2 number,c3 number);

Table created.

SQL> create table jvd_t2 (d1 number, d2 number);

Table created.

SQL> alter table jvd_t2 add primary key (d1);

Table altered.
*********** the following update works: SQL> update (select t1.*,t2.*
2 from jvd_t1 t1,jvd_t2 t2
3 where c1 = d1 )
4 set c2=d2;

0 rows updated.

• but this update does not: SQL> update (select t1.*,t2.* 2 from jvd_t1 t1,jvd_t2 t2 3 where c1||c3 = d1) -- this concatenation is the only difference 4 set c2=d2; set c2=d2 * ERROR at line 4: ORA-01779: cannot modify a column which maps to a non key-preserved table

Why does the key-preserverdness of a table depend on the way I use the columns to equijoin to the key of another table? Oracle can conclude that for every record of jvd_t1 I will still fetch only one record (at the most) from the lookup-table, so jvd_t1 is still key-preserved.

Oracle 9.2 on Sun OS.

Jaap. Received on Wed May 05 2004 - 13:39:15 CDT