Home » SQL & PL/SQL » SQL & PL/SQL » MERGE statement and primary key constraint violation
MERGE statement and primary key constraint violation [message #301711] Thu, 21 February 2008 07:50 Go to next message
alina_
Messages: 14
Registered: February 2008
Junior Member
Hi all,


I am trying to use MERGE statement to update existing table by the rows from an external table but it throws the ORA-00001: unique constraint (ABC.PK_X) violated. The destination table has a primary key consisting of column 'field1'.


MERGE INTO dest_Table d
USING (SELECT DISTINCT (Field1),
                       Field2
       FROM   Source_Table) s
ON ( s.Field1 = d.Field1 )
WHEN MATCHED THEN UPDATE SET d.Field2 = s.Field2
WHEN NOT MATCHED THEN INSERT (d.Field1,
                              d.Field2)
                      VALUES (s.Field1,
                              s.Field2);



ORA-00001: unique constraint (ABC.PK_X) violated


The same error was thrown when the destination table was empty while there were no duplicate rows in source table?
What is wrong?

Any idea???


Thank you in advance ...
Re: MERGE statement and primary key constraint violation [message #301714 is a reply to message #301711] Thu, 21 February 2008 07:53 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
distinct will apply to the entire result set and not to a particular column. So it sounds like you field1 data in your source table is not unique.

HTH

Regards

Raj
Re: MERGE statement and primary key constraint violation [message #301715 is a reply to message #301711] Thu, 21 February 2008 07:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Source and target may not have the same columns in their primary key.

Regards
Michel
Re: MERGE statement and primary key constraint violation [message #301717 is a reply to message #301714] Thu, 21 February 2008 07:57 Go to previous messageGo to next message
alina_
Messages: 14
Registered: February 2008
Junior Member
Hi Raj,

Thank you for your soon reply.

I used DISTINCT after ORA-00001 error occurred to overcome the problem, but the error still exists also without DISTINCT.



Re: MERGE statement and primary key constraint violation [message #301718 is a reply to message #301717] Thu, 21 February 2008 07:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post the column list of your PK.

Regards
Michel
Re: MERGE statement and primary key constraint violation [message #301719 is a reply to message #301711] Thu, 21 February 2008 07:58 Go to previous messageGo to next message
alina_
Messages: 14
Registered: February 2008
Junior Member
Hi Michel,


Source table is an external table and it has no primary key. Only the destination table has this key.
Re: MERGE statement and primary key constraint violation [message #301721 is a reply to message #301711] Thu, 21 February 2008 08:00 Go to previous messageGo to next message
alina_
Messages: 14
Registered: February 2008
Junior Member
The primary key consists of one field.

The source and destination tables have exactly the same structure, they consists of 2 columns, say 'field1' and 'field2', where 'field1' is a primary key in the destination table.
Re: MERGE statement and primary key constraint violation [message #301723 is a reply to message #301719] Thu, 21 February 2008 08:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I was talking logical source (your select distinct ...). Its logical primary key (or rather unique key) is field1, field2.

Which column is the target primary key? field1? field2? other?

But Raj already answered, you may have several rows in source with same unknown field1 and different field2. That satisfies source unique key (field1,field2) but not target one.

Regards
Michel
Re: MERGE statement and primary key constraint violation [message #301724 is a reply to message #301711] Thu, 21 February 2008 08:07 Go to previous messageGo to next message
alina_
Messages: 14
Registered: February 2008
Junior Member
Also, I have no duplicate values for 'field1' in the source table.
Re: MERGE statement and primary key constraint violation [message #301726 is a reply to message #301724] Thu, 21 February 2008 08:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So why DISTINCT if you have no duplicate.
And we still don't know the target primary key column.

Regards
Michel
Re: MERGE statement and primary key constraint violation [message #301727 is a reply to message #301724] Thu, 21 February 2008 08:11 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
can you post the output of the following two queries ?

select count(field1) from source_table;
select count(distinct field1) from source_table;

Regards

Raj

P.S : As @Michael rightly pointed out why distinct if field1 is unique.

[Updated on: Thu, 21 February 2008 08:11]

Report message to a moderator

Re: MERGE statement and primary key constraint violation [message #301728 is a reply to message #301724] Thu, 21 February 2008 08:13 Go to previous messageGo to next message
alina_
Messages: 14
Registered: February 2008
Junior Member
Does all this mean that if I have a source table without any key and which may contain duplicate entries and have a destination table with a primary key, then I cannot use MERGE?
Re: MERGE statement and primary key constraint violation [message #301729 is a reply to message #301728] Thu, 21 February 2008 08:15 Go to previous messageGo to next message
alina_
Messages: 14
Registered: February 2008
Junior Member
the target primary key is 'field1'. DISTINCT I used to ensure that there would not be any duplicate rows (just double checking) ...
Re: MERGE statement and primary key constraint violation [message #301730 is a reply to message #301728] Thu, 21 February 2008 08:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No, it means you (your data) have to satisfy the constraints that are in your model (and implemented in Oracle) and this is true with any SQL statement including MERGE.

Regards
Michel
Re: MERGE statement and primary key constraint violation [message #301731 is a reply to message #301729] Thu, 21 February 2008 08:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
But as Raj said, this only ensures that there is no (field1,field2) couple duplicates and not there is no field1 duplicates.
And this is what happens, you have field1 duplicates.

Regards
Michel
Re: MERGE statement and primary key constraint violation [message #301733 is a reply to message #301711] Thu, 21 February 2008 08:25 Go to previous messageGo to next message
alina_
Messages: 14
Registered: February 2008
Junior Member
I have some difference:

select count(field1) from source_table;
11518

select count(distinct field1) from source_table;
11515
Re: MERGE statement and primary key constraint violation [message #301734 is a reply to message #301733] Thu, 21 February 2008 08:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So you have 3 duplicates.

Regards
Michel
Re: MERGE statement and primary key constraint violation [message #301737 is a reply to message #301734] Thu, 21 February 2008 08:30 Go to previous messageGo to next message
alina_
Messages: 14
Registered: February 2008
Junior Member
I understand. How to overcome the problem?
Re: MERGE statement and primary key constraint violation [message #301738 is a reply to message #301737] Thu, 21 February 2008 08:34 Go to previous messageGo to next message
alina_
Messages: 14
Registered: February 2008
Junior Member
I get confused. When I used DISTINCT(field1) in SELECT, did not I eliminate duplicates?
Re: MERGE statement and primary key constraint violation [message #301739 is a reply to message #301738] Thu, 21 February 2008 08:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Because DISTINCT applies to ALL expressions in select and not just the first one (or the one in parenthesis).

Regards
Michel
Re: MERGE statement and primary key constraint violation [message #301740 is a reply to message #301737] Thu, 21 February 2008 08:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
How to overcome the problem?

You have several ways depending on what you want to do with these duplicates.
You can remove them from input files.
You can select the one you want in your source select; first, last or any other criteria, you have the power of SQL to do this.
You can use the LOG ERRORS clause and applies several times the merge statement using the exception table. This implies to use a staging table and no more the external table.

Regards
Michel

[Updated on: Thu, 21 February 2008 08:44]

Report message to a moderator

Re: MERGE statement and primary key constraint violation [message #301741 is a reply to message #301739] Thu, 21 February 2008 08:48 Go to previous messageGo to next message
alina_
Messages: 14
Registered: February 2008
Junior Member
Finally you are right ...
Re: MERGE statement and primary key constraint violation [message #301743 is a reply to message #301741] Thu, 21 February 2008 08:50 Go to previous message
alina_
Messages: 14
Registered: February 2008
Junior Member
Finally you are right meaning that I identified these rows with duplicate field1 value ...

Thanks both a lot for your help !
Previous Topic: funda of NULL
Next Topic: ORA Errors
Goto Forum:
  


Current Time: Mon Dec 05 09:12:08 CST 2016

Total time taken to generate the page: 0.10404 seconds