Reputation: 352
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 ?
Upvotes: 0
Views: 1963
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