1 2 3 4
|
SQL> create unique index inv_uidx1 on inv(sku_id);
SQL> insert into inv(sku_id) values (1);
SQL> insert into inv(sku_id) values (1); |
Here’s the corresponding error message that is thrown:
ERROR at line 1:
ORA-00001: unique constraint (INV_MGMT.INV_UIDX1) violated
If you’re asked to troubleshoot this issue, the first place you look is in DBA_CONSTRAINTS for a constraint named INV_UIDX1. However, there is no information:
1 2 3 4 5
| select
constraint_name
from dba_constraints
where constraint_name='INV_UIDX1';
no rows selected |
The “no rows selected” message can be confusing: the error message thrown when you insert into the table indicates that a unique constraint has been violated, yet there is no information in the constraint-related data-dictionary views. In this situation, you have to look at DBA_INDEXES to view the details of the unique index that has been created.
If you want to have information related to the constraint in the DBA/ALL/USER_CONSTRAINTS views, you can explicitly associate a constraint after the index has been created:
SQL> alter table inv add constraint inv_uidx1 unique(sku_id);
In this situation, you can enable and disable the constraint independent of the index. However, because the index was created as unique, the index still enforces uniqueness regardless of whether the constraint has been disabled.
When should you explicitly create a unique index versus creating a constraint and having Oracle automatically create the index? There are no hard and fast rules. I prefer to create a unique-key constraint and let Oracle automatically create the unique index, because then I get information in both the DBA/ALL/USER_CONSTRAINTS views and the DBA/ALL/USER_INDEXES views.
But Oracle’s documentation recommends that if you have a scenario where you’re strictly using a unique constraint to improve query performance, it’s preferable to create only the unique index. This is fine. If you take this approach, just be aware that you may not find any information in the constraint-related data-dictionary views.
Partager