CODEWITHSUNDEEP
oracle-databaseunique-constraint
Marc
Marc

Reputation: 352

ORA-00001 in UPDATE statement without duplicate

I fail to understand the logic of the unique constraint when it's based on 2 fields.

I have the following table named DESCRIPTIONS including 3 columns: ID_DESCRIPTION, NAME, ID_DESCRIPTION_TYPE

Now ID_DESCRIPTION is the primary key, and there is a unique constraint UK_DESCRIPTION on couple (ID_DESCRIPTION, NAME).

If I try to run the following query:

UPDATE DESCRIPTIONS SET NAME = 'USA' WHERE ID_DESCRIPTION = 9255813

I'm getting an ORA-00001 exception, saying that unique constraint UK_DESCRIPTION is violated.

Now this would mean that the couple (9255813,'USA') already exists right ? However, I don't see how this is possible since the ID_DESCRIPTION is a primary key and therefore unique AND the results of the query 

SELECT * FROM  DESCRIPTIONS  WHERE ID_DESCRIPTION = 9255813

only return 1 result, the one I want to update. What am I failing to understand here ?

Constraint snapshot from toad

Upvotes: 0

Views: 1963

Answers (1)

APC
APC

Reputation: 146349

I am going to guess that uk_description is in fact a unique key based on the single column of NAME.

"It is unfortunately not."

Okay, the other explanation is that it is a multi-column key based on a different set of columns from what you think. (NAME, ID_DESCRIPTION_TYPE) would also fit the described behaviour.

To be fair, a unique key on(NAME, ID_DESCRIPTION_TYPE) makes more sense. For example, this is the key you'd want when the table is a single reference data look-up (which is a horrible model but common enough). Whereas a compound key of ID_DESCRIPTION, NAME) would do nothing but undermine the primary key.

Upvotes: 1

Related Questions