Unique constraint violated oracle ошибка

Error message looks like this

Error message => ORA-00001: unique constraint (schema.unique_constraint_name) violated

ORA-00001 occurs when: «a query tries to insert a «duplicate» row in a table». It makes an unique constraint to fail, consequently query fails and row is NOT added to the table.»

Solution:

Find all columns used in unique_constraint, for instance column a, column b, column c, column d collectively creates unique_constraint and then find the record from source data which is duplicate, using following queries:

-- to find <<owner of the table>> and <<name of the table>> for unique_constraint

select *
from DBA_CONSTRAINTS
where CONSTRAINT_NAME = '<unique_constraint_name>';

Then use Justin Cave’s query (pasted below) to find all columns used in unique_constraint:

  SELECT column_name, position
  FROM all_cons_columns
  WHERE constraint_name = <<name of constraint from the error message>>
   AND owner           = <<owner of the table>>
   AND table_name      = <<name of the table>>

    -- to find duplicates

    select column a, column b, column c, column d
    from table
    group by column a, column b, column c, column d
    having count (<any one column used in constraint > ) > 1;

you can either delete that duplicate record from your source data (which was a select query in my particular case, as I experienced it with «Insert into select») or modify to make it unique or change the constraint.

[{«Product»:{«code»:»SS3RA7″,»label»:»IBM SPSS Modeler»},»Business Unit»:{«code»:»BU059″,»label»:»IBM Software w/o TPS»},»Component»:»Modeler Server»,»Platform»:[{«code»:»PF025″,»label»:»Platform Independent»}],»Version»:»Not Applicable»,»Edition»:»»,»Line of Business»:{«code»:»LOB10″,»label»:»Data and AI»}}]

Error ‘ORA-00001: UNIQUE CONSTRAINT (AP.AP_INVOICE_DISTRIBUTIONS_U1) VIOLATED’ During Invoice Validation (Doc ID 2443983.1)

Last updated on JULY 15, 2022

Applies to:

Symptoms

On : 12.1.X/12.2.X version, GST Payables

When User is validating invoice, he is getting following error:-

Unexpected error occurred during Tax Calculation.
Exception: Encountered the error in
JAI_TAX_PROCESSING_PKG.CALCULATE_TAXORA-20001: —1:ORA-00001: unique
constraint
(AP.AP_INVOICE_DISTRIBUTIONS_U1) violated.
Please correct the problem or contact your System Administrator.

In FND Debug log message, following error message has been captured.

JAI.PLSQL.JAI_TAX_DETERMINATION_PKG.jai_insert_ap_inv_dist() ORA-00001:
unique constraint (AP.AP_INVOICE_DISTRIBUTIONS_U1) violated
fnd.plsql.APP_EXCEPTION.RAISE_EXCEPTION.dict_auto_log —1: ORA-00001: unique
constraint (AP.AP_INVOICE_DISTRIBUTIONS_U1) violated
JAI.PLSQL.JAI_TAX_PROCESSING_PKG.CALCULATE_TAX ORA-20001: —1: ORA-00001:
unique constraint (AP.AP_INVOICE_DISTRIBUTIONS_U1) violated

STEPS
————————
The issue can be reproduced at will with the following steps:
1. Create Invoice.
2. Validate the invoice

BUSINESS IMPACT
————————
The issue has the following business impact:
Due to this issue, users cannot make vendor payment on time.

Cause

To view full details, sign in with your My Oracle Support account.

Don’t have a My Oracle Support account? Click to get started!

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.

Oracle offers a comprehensive and fully integrated stack of cloud applications and platform services. For more information about Oracle (NYSE:ORCL), visit oracle.com. пїЅ Oracle | Contact and Chat | Support | Communities | Connect with us | | | | Legal Notices | Terms of Use

Источник

MERGE STATEMENT FAILS WITH ORA-00001 UNIQUE CONSTRAINT VIOLATION (Doc ID 1081283.1)

Last updated on FEBRUARY 02, 2022

Applies to:

Oracle Database — Enterprise Edition — Version 10.2.0.4 to 11.2.0.1.0 [Release 10.2 to 11.2]
Oracle Database — Enterprise Edition — Version 12.1.0.2 to 12.1.0.2 [Release 12.1]
Information in this document applies to any platform.

Symptoms

MERGE STATEMENT AFTER UNCOMMITTED UPDATE FAILS WITH ORA-1

The problem can be reproduced as follows:
1.
Session #1

SQL*Plus: Release 10.2.0.4.0 — Production on Thu Jan 21 16:40:52 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 — Production With
the Partitioning, OLAP, Data Mining and Real Application Testing options

2.
SQL> CREATE TABLE table1
(
ORDER_LINE_ID NUMBER(19) NOT NULL,
click NUMBER NOT NULL,
CONSTRAINT PK3
PRIMARY KEY
( ORDER_LINE_ID)
)
ORGANIZATION INDEX;

3.
SQL> CREATE TABLE TABLE2
(
ORDER_LINE_ID NUMBER,
CLICK NUMBER
);
Table created.

4.
SQL> Insert into TABLE1 (ORDER_LINE_ID, CLICK) Values (10430001, 20);
1 row created.

SQL> Insert into TABLE2 (ORDER_LINE_ID, CLICK) Values (10210001, 10);
1 row created.

SQL> Insert into TABLE2 (ORDER_LINE_ID, CLICK) Values (10210001, 20);
1 row created.

SQL> Insert into TABLE2 (ORDER_LINE_ID, CLICK) Values (10430001, 50);
1 row created.

SQL> commit;
Commit complete.

5.
SQL> UPDATE table1 set click=click+5 WHERE order_line_id=10430001;
1 row updated.

SQL> connect login/password
Connected.
SQL> set autotrace on explain
SQL> MERGE INTO table1 t1
USING(
SELECT
fact.ORDER_LINE_ID,
sum (fact.click
) AS click
FROM table2 fact
GROUP by fact.ORDER_LINE_ID
ORDER BY fact.ORDER_LINE_ID
) t2 ON (
t1.ORDER_LINE_ID = t2.ORDER_LINE_ID
)
WHEN MATCHED THEN
UPDATE SET
t1.click = t2.click+10
WHEN NOT MATCHED THEN
INSERT (
ORDER_LINE_ID,
click
) VALUES (
t2.ORDER_LINE_ID,
t2.click) ;

MERGE INTO table1 t1
(the session is waiting)

7.
Go back to Session #1 and enter commit
SQL> commit;
Commit complete.

Now i see in session 1 the following about the waiting MERGE statement:
*
ERROR at line 1:
ORA-00001: unique constraint (OWNER.PK3) violated

8.
Go to session #2 and re-run the merge sql
SQL> /
2 rows merged.

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|

| 0 | MERGE STATEMENT | | 2 | 130 | 4 (25)|
00:00:01
|

| 1 | MERGE | TABLE1 | | | |
|

| 3 | NESTED LOOPS OUTER | | 2 | 170 | 4 (25)|
00:00:01
|

| 4 | VIEW | | 2 | 52 | 4 (25)|
00:00:01
|

| 5 | SORT GROUP BY | | 2 | 52 | 4 (25)|
00:00:01
|

| 6 | TABLE ACCESS FULL| TABLE2 | 2 | 52 | 3 (0)|
00:00:01
|

|* 7 | INDEX UNIQUE SCAN | PK3 | 1 | 59 | 0 (0)|
00:00:01
|

Note
——
— dynamic sampling used for this statement

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|

| 0 | MERGE STATEMENT | | 2 | 130 | 4 (25)|
00:00:01
|

| 1 | MERGE | TABLE1 | | | |
|

| 3 | NESTED LOOPS OUTER | | 2 | 170 | 4 (25)|
00:00:01
|

| 4 | VIEW | | 2 | 52 | 4 (25)|
00:00:01
|

| 5 | SORT GROUP BY | | 2 | 52 | 4 (25)|
00:00:01
|

| 6 | TABLE ACCESS FULL| TABLE2 | 2 | 52 | 3 (0)|
00:00:01
|

|* 7 | INDEX UNIQUE SCAN | PK3 | 1 | 59 | 0 (0)|
00:00:01
|

Note
——
dynamic sampling used for this statement

WORKAROUND:
————
COMMIT update before running MERGE command.

Cause

To view full details, sign in with your My Oracle Support account.

Don’t have a My Oracle Support account? Click to get started!

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.

Oracle offers a comprehensive and fully integrated stack of cloud applications and platform services. For more information about Oracle (NYSE:ORCL), visit oracle.com. пїЅ Oracle | Contact and Chat | Support | Communities | Connect with us | | | | Legal Notices | Terms of Use

Источник

ORA-00001: unique constraint violated error

Table T has four columns A,B,C,D all are not null. Also, unique constraint is defined on column A & B

but, one of the insert statement got failed with the below error.

Getting DB Error: ORA-00001: unique constraint violated error on inserting the value.

It means already record is existing in the table.

As, thousands of records are getting inserted online in the table at a time, so could not able to trace which record is being inserted.

So, how to find that record in the table which is already existing due to which above unique constraint violation error is raised ?

Answers

Error: ORA 1
Text: unique constraint violated
——————————————————————————-
Cause: An update or insert statement attempted to insert a duplicate key.
Action: Either remove the unique restriction or do not insert the key.
For Trusted Oracle7 configured in DBMS MAC mode, you may see this
message if a duplicate entry exists at a different level.

check in the insert statement which was already in the table.. and also constraint assigned on that column.. if unique constarint then give another value
Thanks

but, I want to find that record in the table and want to delete, so how to find that record ?

Edited by: user640001 on Sep 27, 2010 12:00 AM

can you please post the transaction which you are inserting.

As all transactions are online, so neither can trace the transactions nor have any record details which is creating the problem.

From the db, I came to know that a composite primary key is defined including two columns A,B which is restricting that record.

ALTER TABLE T ADD (
CONSTRAINT T_PK
PRIMARY KEY
(A,B));

Just having the error details and knowing that insert statement is failing due to above constraint

And why do you need to identify the statement which is trying to violate this constraint?

Perhaps starting there will give us a little background information we’re presently missing.

I want to delete that record.

so, I need sql statement to find out that record.

user640001 wrote:
Hi,

I want to delete that record.

so, I need sql statement to find out that record.

The question of «why» still hasn’t really been answered.

If the inserts are coming from an application, and the table has a unique constraint placed on it to protect the data, WHY do you think the application attempting to violate this constraint has «better» data?

If this is indeed the way the application needs to function, then you need to modify the application to account for this.
Instead of the current Which doesn’t attempt to catch the unique constraint error.

Since you do not at present know the data trying to violate the constraint (that’s your entire question) i find it difficult to understand how you could presume to know that it is correct . you know nothing about it so the course you are on seems to be the incorrect one (my opinion anyway).

Источник

ORA-00001: unique constraint error..

Hi There,
We were trying to do an insert when we started having ORA-00001: unique constraint error.. to speed our testing we decided to disable all the constraints on the table; however we still having the same issue.

How can we resolve this please.

Anything else we can do please?

Answers

but isn’t the unique index constraint part of the disabled constraints on the table as shown above?

I’m surprised that after we disabled all constraints on the table, this is still an issue??

We can certainly drop it for the time being.. will keep you posted.

rsar001 wrote:
but isn’t the unique index constraint part of the disabled constraints on the table as shown above?

I’m surprised that after we disabled all constraints on the table, this is still an issue??

We can certainly drop it for the time being.. will keep you posted.

Not if index used by PK was created separately prior to PK:
But by dropping index you are simply delaying the issue. Yes, you will be able to insert, but what’s then? You will not be able to recreate PK — same violation error will be raised.

One should keep an eye out for constraints forced through unique indexes and/or any other objects.

We completely missed out the fact that the constraints here are forced through the unique index created before creating the PK.

Источник

ORA-00001: unique constraint violated

I am getting very strange problem here , i don’t understand where is the issue and what is the solution.

Database version 11.2.0.4.0

I have a table let’s say

tb_legal_arb_execution

Composite primary key (comp_code,sec_requisition_no)

Insert statement for inserting data into the table giving error ORA-00001: unique constraint violated

The get_exe_requisition_no is function which is written inside the package legal_requisition_package.

This legal_requisition_package.get_exe_requisition_no is generating the sec_requisition_no for the table tb_legal_arb_execution

which is SE/0018/C/0715/00002 is correct.

The result of the above query(which is used in the insert statement) is giving the correct result as expected.

If the insert statement written directly like this then record is inserted successfully.

Why the record is not inserting into the table while using the package and function ?

Источник