Unique Constraint vs Unique Index in Oracle

A unique constraint and a unique index in Oracle, on the face of it, look very alike. Both enforce uniqueness, and the unique index seems to piggyback on the constraint when the constraint is created or dropped.

You might wonder how a unique constraint and a unique index differ and/or collaborate for performance and data integrity. Here’s a closer look at unique constraint vs unique index in Oracle.

1. "Unique constraint also creates a unique index" — not always!

When you create a unique constraint on a column in an Oracle table, most often you also get a unique index in the bargain. Beware: this is not always the case. The unique constraint might use an existing index — unique or even non-unique — instead of creating a new one.

See this in action.

Case A: Unique constraint: unique index also created
Case B: Unique constraint: existing index used, no unique index created
Case C: Unique constraint deferrable — non-unique index created

Case A: Unique constraint: unique index also created

The most commonly seen behavior: new table — add a unique constraint to it — boom! a unique index gets added.

SQL> -- Case A: Unique constraint: unique index also created
SQL> create table test
  2  (
  3    id   number
  4  , name varchar2(10)
  5  , dob  date
  6  );

Table created.

SQL> alter table test
  2  add constraint test_uk1 unique (id);

Table altered.

Note that we added a unique constraint, not an index, to the new table TEST. Let’s check the data dictionary for constraints and indexes on TEST.

SQL> -- List constraints
SQL> select constraint_name, constraint_type
  2  from user_constraints
  3  where table_name = 'TEST';

CONSTRAINT_NAME                C
------------------------------ -
TEST_UK1                       U

SQL> -- List indexes
SQL> select index_name, uniqueness
  2  from user_indexes
  3  where table_name = 'TEST';

INDEX_NAME                     UNIQUENES
------------------------------ ---------
TEST_UK1                       UNIQUE

A unique index has been auto-created thanks to the unique constraint. So far so good. Let’s try Case B now.

Case B: Unique constraint: existing index used, no unique index created

New table — add a non-unique index to it — then add a unique constraint.

You expect a new unique index on the table to go with the unique constraint? Oracle has different plans.

SQL> -- Case B: Unique constraint: existing index used
SQL> -- no unique index created
SQL> drop table test;

Table dropped.

SQL> create table test
  2  (
  3    id   number
  4  , name varchar2(10)
  5  , dob  date
  6  );

Table created.

SQL> -- Create a non-unique index
SQL> -- No constraint exists at this time
SQL> create index test_nu1
  2  on test (id, name);

Index created.

SQL> -- Add a constraint after creating
SQL> -- non-unique index
SQL> alter table test
  2  add constraint test_uk1 unique (id);

Table altered.

Check if the unique constraint added a unique index this time.

SQL> -- List constraints
SQL> select constraint_name, constraint_type
  2  from user_constraints
  3  where table_name = 'TEST';

CONSTRAINT_NAME                C
------------------------------ -
TEST_UK1                       U

SQL> -- List indexes
SQL> select index_name, uniqueness
  2  from user_indexes
  3  where table_name = 'TEST';

INDEX_NAME                     UNIQUENES
------------------------------ ---------
TEST_NU1                       NONUNIQUE

No new index got added. The unique constraint is going to get along fine with the existing non-unique index TEST_NU1.

Case C: Unique constraint deferrable — non-unique index created

Do exactly as Case A: new table – no index – add a unique constraint, except make the unique constraint DEFERRABLE.

SQL> -- Case C: Unique constraint deferrable
SQL> -- non-unique index created
SQL> drop table test;

Table dropped.

SQL> create table test
  2  (
  3    id   number
  4  , name varchar2(10)
  5  , dob  date
  6  );

Table created.

SQL> -- Create a unique index as deferrable
SQL> -- No index at this time
SQL> alter table test
  2  add constraint test_uk1 unique (id) deferrable;

Table altered.

In the very similar Case A, Oracle had auto-created a unique index for the unique constraint. What does it do here?

SQL> -- List constraints
SQL> select constraint_name, constraint_type
  2  from user_constraints
  3  where table_name = 'TEST';

CONSTRAINT_NAME                C
------------------------------ -
TEST_UK1                       U

SQL> -- List indexes
SQL> select index_name, uniqueness
  2  from user_indexes
  3  where table_name = 'TEST';

INDEX_NAME                     UNIQUENES
------------------------------ ---------
TEST_UK1                       NONUNIQUE

Since the unique constraint is DEFERRABLE, Oracle has created a non-unique index instead of a unique one.

So you see, a unique constraint in Oracle does not always create a unique index along with it. Sometimes the unique constraint uses an existing index – which may not even be unique – or creates a non-unique index if it suits its purpose better.

2. Unique constraint is for data integrity. Unique index is for performance.

Though unique constraints and unique indexes both help with uniqueness, their objectives differ.

A unique constraint is meant to enforce data integrity. A unique constraint might create a unique index implicitly, but it does not rely on or need the index to maintain data integrity.

A unique index serves to make data access efficient. Its primary purpose is to help performance, even if it does maintain uniqueness as a corollary.

3. Unique constraint and unique index are filed separately in the data dictionary

Unique constraints are listed in USER_CONSTRAINTS (or ALL_CONSTRAINTS or DBA_CONSTRAINTS).

SQL> desc user_constraints
 Name                    Null?    Type
 ----------------------- -------- ----------------
 OWNER                            VARCHAR2(120)
 CONSTRAINT_NAME         NOT NULL VARCHAR2(30)
 CONSTRAINT_TYPE                  VARCHAR2(1)
 TABLE_NAME              NOT NULL VARCHAR2(30)
 SEARCH_CONDITION                 LONG
 R_OWNER                          VARCHAR2(120)
 R_CONSTRAINT_NAME                VARCHAR2(30)
 DELETE_RULE                      VARCHAR2(9)
 STATUS                           VARCHAR2(8)
 DEFERRABLE                       VARCHAR2(14)
 DEFERRED                         VARCHAR2(9)
 VALIDATED                        VARCHAR2(13)
 GENERATED                        VARCHAR2(14)
 BAD                              VARCHAR2(3)
 RELY                             VARCHAR2(4)
 LAST_CHANGE                      DATE
 INDEX_OWNER                      VARCHAR2(30)
 INDEX_NAME                       VARCHAR2(30)
 INVALID                          VARCHAR2(7)
 VIEW_RELATED                     VARCHAR2(14)

Unique indexes are listed in USER_INDEXES (or ALL_INDEXES or DBA_INDEXES).

SQL> desc user_indexes
 Name                    Null?    Type
 ----------------------- -------- ----------------
 INDEX_NAME              NOT NULL VARCHAR2(30)
 INDEX_TYPE                       VARCHAR2(27)
 TABLE_OWNER             NOT NULL VARCHAR2(30)
 TABLE_NAME              NOT NULL VARCHAR2(30)
 TABLE_TYPE                       VARCHAR2(11)
 UNIQUENESS                       VARCHAR2(9)
 COMPRESSION                      VARCHAR2(8)
 PREFIX_LENGTH                    NUMBER
 TABLESPACE_NAME                  VARCHAR2(30)
 INI_TRANS                        NUMBER
 MAX_TRANS                        NUMBER
 INITIAL_EXTENT                   NUMBER
 NEXT_EXTENT                      NUMBER
 MIN_EXTENTS                      NUMBER
 MAX_EXTENTS                      NUMBER
 PCT_INCREASE                     NUMBER
 PCT_THRESHOLD                    NUMBER
 INCLUDE_COLUMN                   NUMBER
 FREELISTS                        NUMBER
 FREELIST_GROUPS                  NUMBER
 PCT_FREE                         NUMBER
 LOGGING                          VARCHAR2(3)
 BLEVEL                           NUMBER
 LEAF_BLOCKS                      NUMBER
 DISTINCT_KEYS                    NUMBER
 AVG_LEAF_BLOCKS_PER_KEY          NUMBER
 AVG_DATA_BLOCKS_PER_KEY          NUMBER
 CLUSTERING_FACTOR                NUMBER
 STATUS                           VARCHAR2(8)
 NUM_ROWS                         NUMBER
 SAMPLE_SIZE                      NUMBER
 LAST_ANALYZED                    DATE
 DEGREE                           VARCHAR2(40)
 INSTANCES                        VARCHAR2(40)
 PARTITIONED                      VARCHAR2(3)
 TEMPORARY                        VARCHAR2(1)
 GENERATED                        VARCHAR2(1)
 SECONDARY                        VARCHAR2(1)
 BUFFER_POOL                      VARCHAR2(7)
 FLASH_CACHE                      VARCHAR2(7)
 CELL_FLASH_CACHE                 VARCHAR2(7)
 USER_STATS                       VARCHAR2(3)
 DURATION                         VARCHAR2(15)
 PCT_DIRECT_ACCESS                NUMBER
 ITYP_OWNER                       VARCHAR2(30)
 ITYP_NAME                        VARCHAR2(30)
 PARAMETERS                       VARCHAR2(1000)
 GLOBAL_STATS                     VARCHAR2(3)
 DOMIDX_STATUS                    VARCHAR2(12)
 DOMIDX_OPSTATUS                  VARCHAR2(6)
 FUNCIDX_STATUS                   VARCHAR2(8)
 JOIN_INDEX                       VARCHAR2(3)
 IOT_REDUNDANT_PKEY_ELIM          VARCHAR2(3)
 DROPPED                          VARCHAR2(3)
 VISIBILITY                       VARCHAR2(9)
 DOMIDX_MANAGEMENT                VARCHAR2(14)
 SEGMENT_CREATED                  VARCHAR2(3)

4. There may be scenarios when you can create a unique index but not a unique constraint

You might want to enforce uniqueness conditionally: for example, in a table that supports logical deletion using an ACTIVE flag (Y/N), the data in ID column needs to be unique only if ACTIVE = Y.

You can enforce uniqueness here using a function-based index.

SQL> drop table test;

Table dropped.

SQL>  -- Table with logical deletion
SQL>  -- implemented via "active" flag (Y/N)
SQL>  create table test
  2   (
  3     id     number
  4   , name   varchar2(10)
  5   , dob    date
  6   , active char(1) default 'Y'
  7     check (active in ('Y','N'))
  8  );

Table created.

SQL> -- Function-based index - unique when active = Y
SQL> create unique index test_uf1
  2  on test
  3  (case when active = 'Y'
  4     then id
  5     else null
  6   end);

Index created.

In pre-11g Oracle [i.e. no virtual columns – more on this in a later post], you cannot add a similar conditional unique constraint.

5. You can create foreign keys over a unique constraint, not over a unique index

For referential integrity, you must have a unique constraint. Only a unique index will not do.

Given the table TEST with a unique index and without a unique constraint, try to link a child table to it via a foreign key.

Parent table:

SQL> drop table test;

Table dropped.

SQL> create table test
  2  (
  3    id   number
  4  , name varchar2(10)
  5  , dob  date
  6  );

Table created.

SQL>
SQL> -- Create unique index, not unique constraint
SQL> create unique index test_u1
  2  on test (id);

Index created.

Child table:

SQL> create table child_test
  2  (
  3    a          number
  4  , parent_id  number
  5  );

Table created.

Verify that the parent table has unique index, but no unique constraint:

SQL> -- No unique constraint on parent table
SQL> select constraint_name, constraint_type
  2  from user_constraints
  3  where table_name = 'TEST';

no rows selected

SQL> -- Unique index on parent table
SQL> select index_name, uniqueness
  2  from user_indexes
  3  where table_name = 'TEST';

INDEX_NAME                     UNIQUENES
------------------------------ ---------
TEST_U1                        UNIQUE

Try enabling foreign key from child table to parent:

SQL> -- Try enabling foreign key constraint referencing
SQL> -- table with unique index, not unique constraint
SQL> -- 
SQL> alter table child_test
  2  add constraint test_fk1
  3  foreign key(parent_id)
  4    references test(id);
  references test(id)
                  *
ERROR at line 4:
ORA-02270: no matching unique or primary key for
this column-list

We get the error:

ORA-02270: no matching unique or primary key for this column-list

Let’s add a unique constraint on the parent table and try again:

SQL> -- Add unique constraint
SQL> alter table test
  2  add constraint test_uk1 unique (id);

Table altered.

SQL> -- Try enabling foreign key constraint referencing
SQL> -- table with unique constraint on referenced column 
SQL> alter table child_test
  2  add constraint test_fk1
  3  foreign key(parent_id)
  4    references test(id);

Table altered.

…which goes to show that even if a unique index acts like a unique constraint in enforcing data uniqueness, it isn’t really a constraint — a foreign key cannot be created to it.

Unique Constraint vs Unique Index in Oracle

1. "Unique constraint also creates a unique index" — not always!

Case A: Unique constraint: unique index also created

Case B: Unique constraint: existing index used, no unique index created

Case C: Unique constraint deferrable — non-unique index created

2. Unique constraint is for data integrity. Unique index is for performance.

3. Unique constraint and unique index are filed separately in the data dictionary

4. There may be scenarios when you can create a unique index but not a unique constraint

5. You can create foreign keys over a unique constraint, not over a unique index

For Further Reading

Oracle Forums

Blogroll

[Recommended] JSON in Oracle