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.
For Further Reading
- Conditional Unique Index on Multiple Columns
- ORA-01450: Maximum Key Length Exceeded During Index Creation
