Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm getting "ORA-01779: cannot modify a column which maps to a non key-preserved table" when I try to update a join. I've searched around the site and found a lot of advice on what key-preserved means and why it is necessary... but as near as I can tell I'm complying with that advice, and still getting the error.

I have two tables:

PG_LABLOCATION has, among other things, the columns:
"LABLOCID" NUMBER,
"DNSNAME" VARCHAR2(200 BYTE)

LABLOCID is the primary key, DNSNAME has a unique constraint

PG_MACHINE has, among other things, the columns:
"MACHINEID" NUMBER, 
"LABLOCID" NUMBER, 
"IN_USE" NUMBER(1,0) DEFAULT 0, 
"UPDATE_TIME" TIMESTAMP (6) DEFAULT '01-JAN-1970'

MACHINEID is a primary key
LABLOCID is a foreign key into LABLOCID in PG_LABLOCATION (its primary key)

The update I'm running is:

update 
  (select mac.in_use, mac.update_time
     from pg_machine mac 
     inner join pg_lablocation loc
       on mac.lablocid = loc.lablocid
     where loc.dnsname = 'value'
       and '02-JAN-2013' > mac.update_time
  )
set in_use = 1 - MOD( 101, 2 ), update_time = '02-JAN-2013';

I'm only updating values in one table (PG_MACHINE) and the join column in the other table is the primary key, which should make it key-preserved by my reading. I'm concerned that the where clause is causing the problem, but I tried removing the filter on mac.update_time and got the same error, and loc.dnsname has a unique constraint.

What's even odder is that we have, like many folks, a dev and a prod environment. We did a complete schema and data migration from prod to dev. I've looked them both over and they have identical indexes and constraints. The query works in dev, but generates the above error in prod.

So two questions:

1) Can you see what's wrong with my query? 2) Can you suggest what might be different between my dev and prod environment (e.g. server settings) that could cause this error in one but not the other?

share|improve this question
1  
No unique index on PG_MACHINE.LABLOCID ? –  igr Mar 28 '13 at 6:57
    
Yes, there is. Mea culpa; I was trying to pare down the description of the problem to forum length, and cut too deep. –  Rob Mar 28 '13 at 19:13
    
I posted 1st this answer, and somebody thought it's not good enough and than deleted it and moved (after, it seems) to comment. Ah, it's difficult life of one answer... –  igr Mar 28 '13 at 20:07

1 Answer 1

up vote 2 down vote accepted

You can update a join in Oracle if the following conditions are met:

  1. Only one base table is updated
  2. All other tables are key-preserved: each of them must have at most one row for each row of the base table.

(additional restrictions on updating views apply)

In your example you update table PG_MACHINE only. Oracle has to make sure that for a single row of this table, only one row of the other can be found. This seems to be the case since you have a PK on PG_LABLOCATION.LABLOCID. Therefore you should be able to update the join. See for example this SQLFiddle with a similar setup.

In your case you should either:

  • make sure that the primary key is enabled, validated, not deferrable (interestingly, a deferrable constraint prevents Oracle from updating the join!)
  • use MERGE if PG_LABLOCATION.LABLOCID is unique for the relevant query. MERGE is less strict than update with joins and will only return an error if there is actually a duplicate in the result set (whereas UPDATE will fail if there is the possibility of a duplicate).
  • review your query, since you don't need values from the parent table in the SELECT clause, you could rewrite it as a semi-join (that guarantees that no duplicate will be generated):

    UPDATE (SELECT mac.in_use, mac.update_time
              FROM pg_machine mac
             WHERE mac.lablocid IN (SELECT loc.lablocid 
                                      FROM pg_lablocation loc 
                                     WHERE loc.dnsname = 'value')
               AND to_date('02-JAN-2013') > mac.update_time)
       SET in_use = 1 - MOD(101, 2), 
           update_time = to_date('02-JAN-2013');
    

    This could be rewritten as:

    UPDATE pg_machine mac
       SET in_use = 1 - MOD(101, 2), 
           update_time = to_date('02-JAN-2013')
     WHERE mac.lablocid IN (SELECT loc.lablocid 
                              FROM pg_lablocation loc 
                             WHERE loc.dnsname = 'value')
       AND to_date('02-JAN-2013') > mac.update_time;
    

In this case I would go with the third option: in general you can't update the parent in a parent-child join.

share|improve this answer
    
Thanks for the advice! Unfortunately, I've led you astray; PG_MACHINE.LABLOCID does have a unique constraint on it, which I failed to catch and mention when writing it up. But I tried your semi-join rewrite anyways - I'd shied away from that structure thinking it was a correlated subquery, but of course it isn't - and it worked a treat. I'd still dearly love to know why it runs differently in our dev environment than it does in prod, and why the unique constraint doesn't make the table key-preserved, but I'll certainly settle for "It works now" and be happy. –  Rob Mar 28 '13 at 18:23
    
(The varchar to date conversion is just a result of me stripping the problem down to its essence for the forum; the real case lives in a trigger and replaces 101, 'value', and the date in both places with variables of the appropriate type. But I do appreciate the advice all the same.) (Also, apologies for not upvoting your response; apparently I'm not reputable enough.) –  Rob Mar 28 '13 at 18:24
1  
@rob I got confused with parent-child. Your update should succeed, see this SQLFiddle example, you don't even need a unique constraint on PG_MACHINE.LABLOCID !! The error message could come from a deferrable constraint in one environment, see this other SQLFiddle. About the date conversion, you can use this notation for literal dates =) –  Vincent Malgrat Mar 29 '13 at 9:53

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.