Solved

OCIError: ORA-00001: unique constraint violated

Posted on 2014-09-18
9
918 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Sql Stored Procedure field variable 17 30
What's wrong with this T-SQL Foreign Key? 7 41
Query Help - MSSQL - Averages 5 25
Creation date for a PDB 5 18
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
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
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

785 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