wood burning stoves*
The moose likes JDBC and the fly likes Somtimes I get an unique constraint error on an update statement Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Somtimes I get an unique constraint error on an update statement" Watch "Somtimes I get an unique constraint error on an update statement" New topic
Author

Somtimes I get an unique constraint error on an update statement

Anthony Smith
Ranch Hand

Joined: Sep 10, 2001
Posts: 285
How is this possible. It happens every now and then.
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

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?


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

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


Gravitation cannot be held responsible for people falling in love ~ Albert Einstein
Anthony Smith
Ranch Hand

Joined: Sep 10, 2001
Posts: 285
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

Joined: Apr 14, 2004
Posts: 10336


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 ]
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Somtimes I get an unique constraint error on an update statement