unique constraints and unique indexes
When you create a unique constraint on a table, Oracle database implicitly creates a unique index using the same name as the constraint to enforce the constraint. If you do not specify the constraint name, Oracle will provide one for you with a SYS% naming convention, and the index will also have a system generated name.
alter table unique_test add constraint uk_unique_test unique (num);
select index_name, uniqueness from user_indexes where table_name = 'UNIQUE_TEST';
INDEX_NAME UNIQUENESS
—————————— ———
UK_UNIQUE_TEST UNIQUE
select constraint_name, constraint_type, index_name from user_constraints where table_name = 'UNIQUE_TEST';
CONSTRAINT_NAME C INDEX_NAME
—————————— – ——————————
UK_UNIQUE_TEST U UK_UNIQUE_TEST
If there is a preexisting index that can be used, the database will create the unique constraint, and tell it to use the existing index without creating a new one unnecessarily. Look at the INDEX_NAME column of the DBA_CONSTRAINTS view to see which index the constraint is using. The preexisting index does not have to be unique.
create index i_unique_test on unique_test(num);
alter table unique_test add constraint uk_unique_test unique (num);
select index_name, uniqueness from user_indexes where table_name = 'UNIQUE_TEST';
INDEX_NAME UNIQUENESS
—————————— ———
I_UNIQUE_TEST NONUNIQUE
select constraint_name, constraint_type, index_name from user_constraints where table_name = 'UNIQUE_TEST';
CONSTRAINT_NAME C INDEX_NAME
—————————— – ——————————
UK_UNIQUE_TEST U I_UNIQUE_TEST
You can have a unique index on a table without a unique constraint. This unique index still seems to perform the same duties as a unique constraint will, even claiming that you have violated a unique constraint, not a unique index.
create unique index unique1 on unique_test(num);
select index_name, uniqueness from user_indexes where table_name = 'UNIQUE_TEST';
INDEX_NAME UNIQUENESS
—————————— ———
UNIQUE1 UNIQUE
select constraint_name, constraint_type, index_name from user_constraints where table_name = 'UNIQUE_TEST';
no rows selected
insert into unique_test values (1);
insert into unique_test values (1)
*
ERROR at line 1:
ORA-00001: unique constraint (MICHAEL.UNIQUE1) violated
