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 is the primary key, DNSNAME has a unique constraint

PG_MACHINE has, among other things, the columns:

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

The update I'm running is:

  (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?

  • 2
    No unique index on PG_MACHINE.LABLOCID ?
    – igr
    Commented Mar 28, 2013 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
    Commented Mar 28, 2013 at 19:13

1 Answer 1


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.

  • 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
    Commented Mar 28, 2013 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
    Commented Mar 28, 2013 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 =) Commented Mar 29, 2013 at 9:53
  • None of the links in @VincentMalgrat's answers or comments are working
    – Anuswadh
    Commented Dec 28, 2022 at 21:12
  • @Anuswadh: I fixed the links to point to the most recent version of the documentation Commented Jan 4, 2023 at 10:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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