Oracle unique constraint error when inserting

Question

INSERT INTO SS_ALERT_EVENTS (  ALERT_ID, EVENT_ID, TIME_DURATION, ALERT_EVENT_EFFECT, DATASET_ASSIGN_RULE, KEY_FIELDS_ASSIGN_RULE, SIDE, ALERT_VALIDATION_RULE, UNIQUE_ID ) VALUES ( 'test1', 7 ,  0, 1 ,  NULL,  '5b414c4552545f494e535452554d454e542e496e737472756d656e742049445d203a3d205b54524144455f5245504f52542e496e737472756d656e742049445d3b',  -1,  '5b414c4552542e416374696f6e5d203a3d20313b', 1)
*
ERROR at line 1:
ORA-00001: unique constraint (ESV31SURV.PK_SS_ALERT_EVENTS) violated

The EVENT_ID field is the problem. But I want to insert it anyway. However, when I try to drop the constraint of that name, it says there is no such constraint. Further, no such constraint is shown in USER_CONSTRAINTS table. What should I do?

If you don't see an object it doesn't mean it not exists - it just means that it was created from another schema. Log in as sys and look at constraints list again. — zerkms
– zerkms, Commented Mar 5, 2011 at 12:46

score 3 · Accepted Answer · 2011-03-05 13:37:29Z

3

The unique constraint might be in fact be a primary key constraint - at least that's what the name suggests.

Dropping the primary key of a table will potentially have very bad side effect it might break applications that rely on this primary key (and you will also have to drop all foreign keys that reference that table before you can drop the primary key)

That primary key was created with a purposes, so before blindly dropping it you should consult whoever created that schema and make sure that primary key is not needed (or should be redefined).

Having said all this: try to drop the PK using

ALTER TABLE SS_ALERT_EVENTS 
  DROP PRIMARY KEY

But please double check if this is really a wise decision!

edited Mar 5, 2011 at 13:37

answered Mar 5, 2011 at 13:18

user330315

Sign up to request clarification or add additional context in comments.

5 Comments

zerkms Over a year ago

"The unique constraint is in fact a primary key constraint - at least that's what the name suggests. " --- wrong. Look one more time at the query. It is not a primary key, it is a regular unique constraint.

user330315 Over a year ago

@zerkms: a primary key is a unique constraint. Violating a PK constraint will throw an ORA-0001 just as violating a "regular" unique constraint. Try it out, both will give you exactly the same error

zerkms Over a year ago

sorry, i need glasses :-( In some unknown reason I haven't seen in the first time that the constraint name ESV31SURV.PK_SS_ALERT_EVENTS contains PK prefix. Sorry. +1

user330315 Over a year ago

@zerkms: A PK is also a unique constraint whereas a unique constraint is not necessarily a PK - I agree with you there. But from the error message you cannot tell whether the constraint is a PK or a "just" a unique constraint. Again: try it out. Both will throw ORA-0001. And the naming conventions usually used in the Oracle world do prefix the PK constraint with a PK - that's why I said "that's what the name suggests". I can be a "regular" unique constraint but it can also be a PK constraint

zerkms Over a year ago

yes, yes, yes, you're absolutely correct. I just missed the PK part of constraint and thought it is a regular unique, sorry once again ;-) "But from the error message" --- sure, I proposed to look at the query which contains the table name + fields names and event_id obviously is a bad name for the table with SS_ALERT_EVENTS name. At least from my point of view.

Collectives™ on Stack Overflow

Oracle unique constraint error when inserting

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related