Solved

OCIError: ORA-00001: unique constraint violated

Posted on 2014-09-18
9
878 Views
Last Modified: 2014-10-07
Hi Experts,

I am having an unique constraint issue during insertion in ruby on rails app. (The insertion doesn't work all the time. It works occasionally though.) The error message is:

OCIError: ORA-00001: unique constraint (ABCSIGN_DBA.PK_ATTEND_IP) violated: INSERT INTO attend_ip (xxx, xxx, xxx, .........)

I checked constraints tab for the ATTEND_IP table in Oracle SQL Developer and I don't see any name "PK_ATTEND_IP". All the names are:
SYS_XXXXXX, SYS_XXXXXX, .....

and their types are all checking not null.

There are a few other DB tables, but they are not related to ATTEND_IP table.

Now, based on the error message, how do I find this unique constraint that might cause this issue on the database.

thanks in advance.
0
Comment
Question by:dkim18
  • 4
  • 3
9 Comments
 
LVL 32

Expert Comment

by:ste5an
ID: 40331526
The PK prefix tells me, that it is a violation of the primary key. Seems like you're trying to insert the same row either again or some kind of replay happens..
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40331593
If you are logged in as ABCSIGN_DBA then:
select * from user_cons_columns where constraint_name='PK_ATTEND_IP';

If a DBA level account:
select * from dba_cons_columns where constraint_name='PK_ATTEND_IP' and owner='ABCSIGN_DBA';
0
 

Author Comment

by:dkim18
ID: 40331600
slightwv,

I tried both and I got:

no rows selected
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 40331670
It sort of has to work.  Make sure you are connecting to the same database that is generating the error.

Here is a complete test case (connecting to the database as SCOTT) that shows it working:
drop table tab1 purge;

create table tab1(
	col1 number,
	constraint tab1_pk PRIMARY KEY (col1)
);

insert into tab1 values(1);
--generate the error to show the PK is working
insert into tab1 values(1);
commit;

select * from user_cons_columns where constraint_name='TAB1_PK';

select * from dba_cons_columns where constraint_name='TAB1_PK' and owner='SCOTT';

Open in new window

0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:dkim18
ID: 40331683
This is what I got:

Error starting at line : 5 in command -
drop table tab1 purge
Error report -
SQL Error: ORA-00942: table or view does not exist
00942. 00000 -  "table or view does not exist"
*Cause:    
*Action:
table TAB1 created.
1 rows inserted.
Error starting at line : 14 in command -
insert into tab1 values(1)
Error report -
SQL Error: ORA-00001: unique constraint (ABCSIGN_DBA.TAB1_PK) violated
00001. 00000 -  "unique constraint (%s.%s) violated"
*Cause:    An UPDATE or INSERT statement attempted to insert a duplicate key.
           For Trusted Oracle configured in DBMS MAC mode, you may see
           this message if a duplicate entry exists at a different level.
*Action:   Either remove the unique restriction or do not insert the key.
committed.

OWNER                          CONSTRAINT_NAME                TABLE_NAME                   COLUMN_NAME                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        POSITION
------------------------------ ------------------------------ ------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------
ABCSIGN_DBA                  TAB1_PK                        TAB1                           COL1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      1 

OWNER                          CONSTRAINT_NAME                TABLE_NAME                     COLUMN_NAME                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        POSITION
------------------------------ ------------------------------ ------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------
ABCSIGN_DBA                  TAB1_PK                        TAB1                           COL1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      1

Open in new window

0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40331694
>>This is what I got:

OK, you see that I named a primary key and the select displayed the columns in that primary key.

Make sure the constraint name and owner in the select you are issuing is all upper case.

If so and the select doesn't work given your constraint name/owner, then I'm thinking the database generating the error isn't the one you are querying.
0
 

Author Comment

by:dkim18
ID: 40339519
This is weird.
The database generating the error IS the one that I am querying, because if I hit f5 key a few times after the error(on the web app), the insertion actually works. When I check the table and I see a new row is inserted.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40339538
I would think you have an error in the app.  I cannot even begin to guess why multiple F5's eventually cause the insert to work.

The question asked is how to locate the columns in the constraint that is causing the error.

I showed that selecting the constraint by name will work.  The fact you cannot see a constraint named PK_ATTEND_IP means you aren't looking in the right place.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now