Solved

OCIError: ORA-00001: unique constraint violated

Posted on 2014-09-18
9
943 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 33

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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
 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
T-SQL: New to using transactions 9 46
ORA-02288: invalid OPEN mode 2 56
MS SQL Update query with connected table data 3 38
Syntax Issue with SSIS module 26 103
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to recover a database from a user managed backup

861 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