A question was recently asked by a colleague regarding unique indexes and constraints. He was complaining that the unique index did not create the associated constraint by default. Unlike the situation when a unique constraint is created (the associated unique index is created if no other index is specified) there really isn’t any reason for Oracle to create a unique constraint for a given unique index. Also, it’s possible to create a unique constraint that uses a non-unique index. Let’s explore this further.
Whether you have a unique index or a unique constraint the following error message will appear if you violate the uniqueness:
ORA-00001: unique constraint ... violated
The confusing part is probably the report by Oracle that a unique constraint has been violated. This message can report the constraint name (for unique and primary key constraints) or the index name (for unique indexes in the absence of a constraint). The first act a DBA would likely perform is a check of the DBA_CONSTRAINTS view to search for the reported constraint; since the index name is reported rather than the constraint name no such constraint exists. Hopefully the DBA would then go to DBA_INDEXES to search for a unique index that may have been created to enforce uniqueness, which is what this colleague did. Let’s go through three scenarios that may help explain why, when a unique index is created, the associated unique constraint is not.
Let’s start with the warhorse of the Oracle demo tables, EMP. We create the demo tables then add a unique index to EMP:
SQL> -- SQL> -- Make the empno column unique SQL> -- SQL> -- Use an index SQL> -- SQL> -- The expected constraint does not SQL> -- get created by default SQL> -- SQL> SQL> create unique index emp_uq_empno on emp(empno); Index created. SQL> SQL> select index_name, uniqueness 2 from user_indexes; INDEX_NAME UNIQUENES ------------------------------ --------- EMP_UQ_EMPNO UNIQUE SQL> SQL> select owner, table_name, constraint_name, index_name 2 from user_constraints; OWNER TABLE_NAME CONSTRAINT_NAME INDEX_NAME ------------------------------ ------------------------------ ------------------------------ ------------------------------ BING EMP SYS_C007995 SQL>
The constraint reported is a NOT NULL constraint for the EMPNO column, it has nothing to do with the unique index. Notice that no UNIQUE constraint exists. So let’s now violate that unique index and see what Oracle reports:
SQL> -- SQL> -- Violate the uniqueness SQL> -- SQL> -- Oracle reports a constraint violation SQL> -- even though no actual constraint SQL> -- exists SQL> -- SQL> SQL> insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) 2 values (7369, 'BORPO','FLERB',7844,sysdate-121, 900, 0, 20); insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) * ERROR at line 1: ORA-00001: unique constraint (BING.EMP_UQ_EMPNO) violated SQL>
As was noted earlier Oracle reports that a constraint was violated yet reports the name for the unique index created against the EMP table. Let’s change this around a bit and create an actual UNIQUE constraint and see what happens:
SQL> -- SQL> -- Drop the index SQL> -- SQL> -- Create a unique constraint now SQL> -- SQL> -- Notice that the index IS created SQL> -- SQL> SQL> drop index emp_uq_empno; Index dropped. SQL> SQL> alter table emp 2 add constraint emp_uq unique(empno); Table altered. SQL> SQL> select index_name, uniqueness 2 from user_indexes; INDEX_NAME UNIQUENES ------------------------------ --------- EMP_UQ UNIQUE SQL> SQL> select owner, table_name, constraint_name, index_name 2 from user_constraints; OWNER TABLE_NAME CONSTRAINT_NAME INDEX_NAME ------------------------------ ------------------------------ ------------------------------ ------------------------------ BING EMP EMP_UQ EMP_UQ BING EMP SYS_C007995
Now we have both a UNIQUE constraint and a unique index. Violating this reports what we would expect to see from Oracle:
SQL> -- SQL> -- Violate the uniqueness SQL> -- SQL> -- Oracle reports a constraint violation SQL> -- SQL> SQL> insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) 2 values (7369, 'BORPO','FLERB',7844,sysdate-121, 900, 0, 20); insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) * ERROR at line 1: ORA-00001: unique constraint (BING.EMP_UQ) violated SQL>
The error message reports the constraint name that was violated and, as shown, we do have a UNIQUE constraint by that name AND an associated UNIQUE index to go along with it. Oracle correctly assumes that since you want a UNIQUE constraint you also want a UNIQUE index. But it can’t assume that if you create a UNIQUE index you also want a UNIQUE constraint because you can build a UNIQUE constraint with a non-unique index. We drop the existing constraint and start again:
SQL> -- SQL> -- NOW create a unique constraint SQL> -- that uses a non-unique index SQL> -- SQL> SQL> alter table emp drop constraint emp_uq; Table altered. SQL> SQL> create index emp_empno on emp(empno); Index created. SQL> SQL> alter table emp add constraint emp_uq 2 unique (empno) 3 using index emp_empno; Table altered. SQL> SQL> select index_name, uniqueness 2 from user_indexes; INDEX_NAME UNIQUENES ------------------------------ --------- EMP_EMPNO NONUNIQUE SQL> SQL> select owner, table_name, constraint_name, index_name 2 from user_constraints; OWNER TABLE_NAME CONSTRAINT_NAME INDEX_NAME ------------------------------ ------------------------------ ------------------------------ ------------------------------ BING EMP EMP_UQ EMP_EMPNO BING EMP SYS_C007995 SQL>
Once we dropped the UNIQUE constraint the UNIQUE index created as part of that process was also dropped. Now the only index we have on EMP is the non-unique index on EMPNO we created before creating the constraint. Take note that we used the USING INDEX clause of the ADD CONSTRAINT action from ALTER TABLE, to tell Oracle that we have an index we want to use so don’t create the default index. Again let’s violate the uniqueness:
SQL> -- SQL> -- Violate the uniqueness SQL> -- SQL> -- Oracle reports a constraint violation SQL> -- SQL> SQL> insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) 2 values (7369, 'BORPO','FLERB',7844,sysdate-121, 900, 0, 20); insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) * ERROR at line 1: ORA-00001: unique constraint (BING.EMP_UQ) violated SQL>
Here is the interesting part of using a non-unique index to enforce a UNIQUE/PRIMARY KEY constraint: if you drop the constraint the index remains:
SQL> -- SQL> -- Drop the constraint SQL> -- SQL> -- The index remains SQL> -- SQL> SQL> alter table emp drop constraint emp_uq; Table altered. SQL> SQL> select index_name, uniqueness 2 from user_indexes; INDEX_NAME UNIQUENES ------------------------------ --------- EMP_EMPNO NONUNIQUE SQL> SQL> select owner, table_name, constraint_name, index_name 2 from user_constraints; OWNER TABLE_NAME CONSTRAINT_NAME INDEX_NAME ------------------------------ ------------------------------ ------------------------------ ------------------------------ BING EMP SYS_C007998 SQL>
Since there are several ways to implement uniqueness in a column or set of columns in a table, one being to simply create a unique index on the key column or columns, it isn’t necessary to have an associated UNIQUE constraint in force. It may be confusing to not have such a constraint (and I prefer to have such a constraint in place to enforce uniqueness) but it’s not required. As to why Oracle doesn’t automatically create such a constraint when a unique index is created; well, I think it has to do with how that constraint can be created. We can allow Oracle to create the unique index, we can use a non-unique index that already exists, or we could use a separate, existing unique index to keep the number of indexes manageable.
Yes, Oracle could modify the CREATE INDEX statement yet again to include a CONSTRAINT clause but I think that would be one more thing to forget or misuse — not every index needs a constraint connected to it. And how would you police how that parameter is used? UNIQUE and PRIMARY KEY constraints would be logical to create for UNIQUE indexes; maybe the CONSTRAINT option would only be available for CREATE UNIQUE INDEX statements. Again, just because you have a UNIQUE index doesn’t mean you need to have a corresponding UNIQUE constraint.
That’s my story and I’m sticking with it.
So when do I get my raise?