CODEWITHSUNDEEP
oracle-databaseperformanceindexingconstraintsunique
kevin
kevin

Reputation: 65

In Oracle, does the unique constraint include an index implicitly?

this question is for performance issue, For example, if I would add a unique constraint such as:

ALTER TABLE Staffs ADD CONSTRAINT test UNIQUE (Company_Name, Staff_ID);

should I add a unique index for performance issue?

CREATE UNIQUE INDEX test2 ON Staffs (Company_Name, Staff_ID);

For Primary key, I can see there must be a corresponding index in dba_indexes system table, but I have not seen the equivalent for the case unique constraint

Upvotes: 4

Views: 1693

Answers (1)

APC
APC

Reputation: 146209

"I have not seen the equivalent for the case unique constraint"

Hmmmm, are you sure?

SQL> create table t23
  2  (id number
  3   , col1 date)
  4  /

Table created.

SQL> alter table t23
  2  add constraint t23_uk unique (id)
  3  /

Table altered.

SQL> select index_name, uniqueness
  2  from user_indexes
  3  where table_name='T23'
  4  /

INDEX_NAME                     UNIQUENES
------------------------------ ---------
T23_UK                         UNIQUE

SQL>

Note that we can use an existing index, and it doesn't have to be unique. But this means the index name might not match the constraint name (this would also work for primary keys):

SQL> alter table t23 drop constraint t23_uk;

Table altered.

SQL> select index_name, uniqueness
  2  from user_indexes
  3  where table_name='T23'
  4  /

no rows selected

SQL> create index t23_idx on t23(id)
  2  /

Index created.

SQL> select index_name, uniqueness
  2  from user_indexes
  3   where table_name='T23'
  4  /

INDEX_NAME                     UNIQUENES
------------------------------ ---------
T23_IDX                        NONUNIQUE

SQL> alter table t23
  2  add constraint t23_uk unique (id)
  3  /

Table altered.

SQL>

Does the non-unique index enforce the unique constraint? Yes it does:

SQL> insert into t23 values (1, sysdate)
  2  /

1 row created.

SQL> r
  1* insert into t23 values (1, sysdate)
insert into t23 values (1, sysdate)
*
ERROR at line 1:
ORA-00001: unique constraint (APC.T23_UK) violated

SQL> drop index t23_idx 
  2  /
drop index t23_idx
           *
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of unique/primary key


SQL>

We can check the data dictionary to see which index is associated with a constraint:

SQL> select constraint_name, constraint_type, index_name
  2  from user_constraints
  3  where table_name = 'T23'
  4  /

CONSTRAINT_NAME                C INDEX_NAME
------------------------------ - ------------------------------
T23_UK                         U T23_IDX

SQL>

Upvotes: 2

Related Questions