ORA-00001: unique constraint (FES.PEOPLE_I6) violated

[diehippy]

Hi

I get the following error ORA-00001: unique constraint (FES.PEOPLE_I6) violated when I run the following update query

update people p
set (p.gc_candidate_no, p.gc_uci) =
(select gu.cnumber, gu.gc_uci
from
sdc_UCI_update gu
where p.person_code = gu.person_code)
where p.person_code in (select gu2.person_code from sdc_UCI_update gu2)

The people table has person_code as its primary key the other table sdc_uci_update has no primary key

Am I doing something wrong?

Many Thanks in advance for anyones help

Diehippy

 

[ChrisHunt]

It looks like you have a unique constraint on either gc_candidate_no or gc_uci (or possibly both) which you're breaking with your update statement. i.e. you're trying to give two different people the same gc_candidate_no/gc_uci.

You could find the problematic value like this:

SELECT gc_candidate_no,count(*),min(person_code),max(person_code)
FROM sdc_UCI_update
GROUP BY gc_candidate_no
HAVING count(*) > 1

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[SantaMufasa]

Actually, Chris, will the SELECT find the problematic value if the unique constraint is still enabled, thus not allowing a duplicate value?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[ChrisHunt]

What I meant was, if he wants to find out which rows in sdc_UCI_update are causing his update to fail, he can use my query above. Armed with that knowledge he can track down the root cause of the problem - why he's getting duplicate values in a column he expects to be unique.

The constraint will continue to protect the (not) updated people table.

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd