Solved

OCIError: ORA-00001: unique constraint violated

Posted on 2014-09-18
9
1,050 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
9 Comments
 
LVL 34

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 77

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
Setting up LaraDock for Laravel

Learn how to set up LaraDock in a Laravel project - LaraDock gives us an easy way to run a Laravel application using Docker in a single command.

 
LVL 77

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
 

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 77

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 77

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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

622 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