This content has been marked as final. Show 10 replies
Error: ORA 1
Text: unique constraint <table.column> 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
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 (
Just having the error details and knowing that insert statement is failing due to above constraint
thats why iam asking which row you are trying to insert, which column has constraint enabled? post the query which you are trying to insert,
and the details of constraints of a table using user_constraints
select constraint_name, constraint_type from user_constraints where table_name='&table_name';according to that you can delete the existing query... THanks
user640001 wrote:The question of "why" still hasn't really been answered.
I want to delete that record.
so, I need sql statement to find out that record.
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
begin insert into table1 (<columns>) values (<values>); exception when dup_val_on_index then update table1 set <columns_values> where column_a = :variable_a and column_b = :variable_b end;
Which doesn't attempt to catch the unique constraint error.
insert into table1 (<columns>) values (<values>);
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).
As all gurus suggested, I am also not clear about what you want to do. However, to catch the constraint exceptions into a table, you should look at
1. ALTER TABLE table_name ENABLE CONSTRAINT con_name EXCEPTIONS INTO EXCEPTIONS
In that way you can find the errors and the data causing the error.