Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1160
  • Last Modified:

OCIError: ORA-00001: unique constraint violated

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
dkim18
Asked:
dkim18
  • 4
  • 3
1 Solution
 
ste5anSenior DeveloperCommented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
dkim18Author Commented:
slightwv,

I tried both and I got:

no rows selected
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
slightwv (䄆 Netminder) Commented:
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
 
dkim18Author Commented:
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
 
slightwv (䄆 Netminder) Commented:
>>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
 
dkim18Author Commented:
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
 
slightwv (䄆 Netminder) Commented:
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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now