Win a copy of Re-engineering Legacy Software this week in the Refactoring forum
or Docker in Action in the Cloud/Virtualization forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Somtimes I get an unique constraint error on an update statement

 
Anthony Smith
Ranch Hand
Posts: 285
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
How is this possible. It happens every now and then.
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Are you trying to update a record's Primary Key to equal an existing value? Does your database allow any unique constraints on non-key fields? If so, are you breaking that rule by trying to update a field to an existing value?
 
Shailesh Chandra
Ranch Hand
Posts: 1082
Java Oracle Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
In addition to Paul , also check that If there is any unique index on columns, on which you are executing update statements.Some time unique indexes also cause constraint error


Shailesh
 
Anthony Smith
Ranch Hand
Posts: 285
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Are you trying to update a record's Primary Key to equal an existing value?
-- Yes

Does your database allow any unique constraints on non-key fields?
-- The db is Oracle, so I would say yes.

If so, are you breaking that rule by trying to update a field to an existing value?

If I have 15 columns (2 are primary keys), and I try to update with all the same data, I will get this error?

If one non-primary key column is different, then I won't get this error?
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Are you trying to update a record's Primary Key to equal an existing value?
-- Yes

A Primary Key identifies a tuple (a record). If you change the identifier of a tuple, this is analagous to doing an insert, not an update, since a tuple with a different identifier equals a different tuple. If this is what you want to do, you should really delete the first tuple and insert its replacement.


Does your database allow any unique constraints on non-key fields?
-- The db is Oracle, so I would say yes

You are right, it does. In which case you should check your table's DDL ("desc <tablename> " in SQLPlus, or look at it through Enterprise Manager) and see if there are any on the table you are trying to update. If there are you will have to check that you are not violating the constraint before trying to update a tuple. Which you can do with a select statement.


If I have 15 columns (2 are primary keys), and I try to update with all the same data, I will get this error?

You cannot define more than one Primary Key in a table. You may have a Composite Key, which has two identifying fields. Taken together they would define the Primary Key.


It sound like you may be a little shakey on constraints. You might benefit from reading the chapter of the Oracle documentation "Database Concepts: Data Integrity".
[ June 15, 2005: Message edited by: Paul Sturrock ]
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic