Path: newssvr20.news.prodigy.com!newsmst01a.news.prodigy.com!prodigy.com!atl-c02.usenetserver.com!news.usenetserver.com!border1.nntp.ash.giganews.com!nntp.giganews.com!news2.euro.net!postnews1.euro.net!news.wanadoo.nl!not-for-mail From: Jaap W. van Dijk Newsgroups: comp.databases.oracle.server Subject: Why isn't table A always key-preserved if equijoined to the key of table B? Date: Wed, 05 May 2004 20:39:15 +0200 Message-ID: X-Newsreader: Forte Free Agent 1.92/32.572 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Lines: 43 Organization: Wanadoo NNTP-Posting-Date: 05 May 2004 18:39:15 GMT NNTP-Posting-Host: p0893.nas2-asd6.dial.wanadoo.nl X-Trace: 1083782355 willi.euronet.nl 60805 62.234.215.131:1114 X-Complaints-To: abuse@wanadoo.nl Xref: newssvr20.news.prodigy.com comp.databases.oracle.server:260884 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.