ORA-00001: unique constraint violated

Error:
ORA-00001: unique constraint (myschema.UK_mytable) violated
Backtrace:
ORA-06512: at "myschema.mytable", line 4210

UK_mytable is a unique key on the following fields of mytable.

col1,
col2,
col3,
col4.

There is no duplicate on this column combination col1 through col4 in table mytable.

However, duplicate exists in only col2 data.


mytable data:
col1      col2            col3      col4
abc      5K390440            def      ICCOGCF2
uvw      5K390440            xyz      FO785L63


The error is during an update:
update mytable u
set    u.col2   = :new.v_col2
where  u.col2 = :old.col2; -->old.col2 is 8K390640.


should this situation cause ORA-00001 error.

It seems mytable is unique only when all 4 columns are present.
Updating mytable with only one column col2 can raise unique constraint issues, is it true
gram77Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Naveen KumarProduction Manager / Application Support ManagerCommented:
no - only when each column is unique then it will give an error. since here 4 columns together form the uniqueness - it should not give this error.

since you are getting the error, there must be a some data issues in your table.

can you query your table for col2 = 8K390640 and check how many records are there and what values they have for all 4 columns.
0
gram77Author Commented:
col2 = 8K390640 contains 2 rows.
mytable data:
col1      col2                  col3      col4
abc      8K390640         def      ICCOGCF2
uvw      8K390640         xyz      FO785L63
0
Naveen KumarProduction Manager / Application Support ManagerCommented:
update mytable u
set    u.col2   = :new.v_col2
where  u.col2 = :old.col2; -->old.col2 is 8K390640.

What is :new.v_col2 and :old.col2 ?

Is this update statement part of a trigger ?  if Yes, then can you give the trigger code to see what else is there in the trigger and what could be causing this problem.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

slightwv (䄆 Netminder) Commented:
>>:new.v_col2

What is this value?

I will confirm what was said above:  All 4 columns are unique and updating col2 in the trigger will create a duplicate across all 4 columns.

for example: Say you have:
abc      8K390640         def      ICCOGCF2
abc      Hello                def      ICCOGCF2


And the value of :new.v_col2 is 'Hello'.

The update based on :old.col2 of '8K390640'  will now create a duplicate since 'Hello' in col2 already exists.
 
0
gram77Author Commented:
let me clearify

mytable has a unique key on col1, col2, col3 and col4.

update tries to update col2 that has two rows of same value.

mytable data:
col1      col2         col3      col4
abc      5K390440      def      ICCOGCF2
uvw      5K390440      xyz      FO785L63


update mytable u
set    u.col2   = 5K390441
where  u.col2 = 5K390440;

should this cause ORA-00001?
0
slightwv (䄆 Netminder) Commented:
>>update tries to update col2 that has two rows of same value.

Are there only TWO rows in the entire table?

>>should this cause ORA-00001?

If there are more rows in the table, then it can.  Look at the example I posted.  There are two rows that are not unique.  If you try to update one of them the way I posted, it will cause a duplicate row.

In your case, do you already have rows like:

abc      5K390441   def      ICCOGCF2
uvw      5K390441   xyz      FO785L63

If so, the update will cause duplicates.
0
gram77Author Commented:
no, i dont have 5K390441 in col2, only 2 rows of col2=5K390440 as i posted.

There are about 50 rows where col2 has duplicates, but if i take a composite of col1, col2, col3 and col4 there are no duplicates..

should the update statement contain reference to all the 4 columns?
0
slightwv (䄆 Netminder) Commented:
>>should the update statement contain reference to all the 4 columns?

Not really. unless it is necessary to fix the duplicates being created.

>>but if i take a composite of col1, col2, col3 and col4 there are no duplicates..

Not as they currently are but the update you are running will result in the creation of duplicates.  That is the only possibility.  It is a data issue.
0
gram77Author Commented:
the data is correct.
0
slightwv (䄆 Netminder) Commented:
>>but if i take a composite of col1, col2, col3 and col4 there are no duplicates..

As the data sits right now this is correct.  The problem comes with the update.

Please understand what I posted in http:#a37291093.  As the two rows are, there are no duplicates.

When you go to update col2, it will create a duplicate.

>>the data is correct.

Then you wouldn't be getting an error.

The fact that you are getting this error means the update of col2 as you have it will result in a duplicate across all 4 columns.

This is the ONLY thing that will cause this.

How many rows are in the table?
0
htonkovCommented:
Hi!

For testing purposes:

1. disable unique constraint (you will have to drop unique index)
2. perform update
3. create constraint.

Did you get exception after step 3?
If yes: there is data problem
If no: there might be oracle bug (although not likely, because whenever I got similar problem there was some obscure problem with data :) ).

Alternatively you can also try to log errors:

1. create error log table
exec DBMS_ERRLOG.CREATE_ERROR_LOG('mytable ');
2.
update mytable u
set    u.col2   = 5K390441
where  u.col2 = 5K390440
log errors ('test') reject limit unlimited; -- 'test' is just arbitrary text in one additional column in error table
3.
commit;
4
select * ERR$_MYTABLE; -- to see exactly which rows caused error (with rowids).

This might help...

Best regards...
0
gram77Author Commented:
"should this situation cause ORA-00001 error."
No.


If i run an update on the following data:

mytable data:
col1      col2            col3      col4
abc      5K390440            def      ICCOGCF2
uvw      5K390440            xyz      FO785L63


The error is during an update:
update mytable u
set    u.col2   = '5K390441'
where  u.col2 = '5K390440';

This error will not arise.
0
slightwv (䄆 Netminder) Commented:
You have a data error that you do not think you have.  This is the ONLY thing that will cause this.

Please look at the example below.  This MUST be what is causing the issue.

The two sample rows you posted will update just fine.  There are only two rows with 5K390440.

If I add a third row that doesn't have 5K390440 in it, the update will fail.
drop table tab1 purge;

create table tab1(col1 char(3), col2 char(8), col3 char(3), col4 char(8),
	constraint tab1_pk primary key (col1, col2, col3, col4)
);

insert into tab1 values('abc','5K390440','def','ICCOGCF2');
insert into tab1 values('uvw','5K390440','xyz','FO785L63');
commit;


--runs fine
update tab1 u set    u.col2   = '5K390441' where  u.col2 = '5K390440';

rollback;

--add another row to show what CAN produce the error
insert into tab1 values('abc','5K390441','def','ICCOGCF2');

--generates an error
update tab1 u set    u.col2   = '5K390441' where  u.col2 = '5K390440';

rollback;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gram77Author Commented:
col1      col2      col3      col4
uvw      5K390440      xyz      FO785L63
abc      5K390440      def      ICCOGCF2
abc      5K390441      def      ICCOGCF2

This update convert the second row such that col1, col2, col3 and col4 combination does not remain unique, so the error.

update tab1 u
set    u.col2   = '5K390441'
where  u.col2 = '5K390440';
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.