Oracle: Sorting Out Constraints

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?

See all articles by David Fitzjarrell

David Fitzjarrell
David Fitzjarrell
David Fitzjarrell has more than 20 years of administration experience with various releases of the Oracle DBMS. He has installed the Oracle software on many platforms, including UNIX, Windows and Linux, and monitored and tuned performance in those environments. He is knowledgeable in the traditional tools for performance tuning – the Oracle Wait Interface, Statspack, event 10046 and 10053 traces, tkprof, explain plan and autotrace – and has used these to great advantage at the U.S. Postal Service, American Airlines/SABRE, ConocoPhilips and SiriusXM Radio, among others, to increase throughput and improve the quality of the production system. He has also set up scripts to regularly monitor available space and set thresholds to notify DBAs of impending space shortages before they affect the production environment. These scripts generate data which can also used to trend database growth over time, aiding in capacity planning. He has used RMAN, Streams, RAC and Data Guard in Oracle installations to ensure full recoverability and failover capabilities as well as high availability, and has configured a 'cascading' set of DR databases using the primary DR databases as the source, managing the archivelog transfers manually and montoring, through scripts, the health of these secondary DR databases. He has also used ASM, ASMM and ASSM to improve performance and manage storage and shared memory.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles