Unique Records In Oracle

Defining unique records in an Oracle table is necessary for employee data, manufacturing part numbers and user names, to name a few examples. There are several ways this can be accomplished in the database but one of these methods may fail for purchased applications that use the data dictionary to verify such a constraint is in force. Let’s look at the various ways uniqueness can be defined and which method may not be acceptable to third-party applications.

The easiest and most common method is to declare a primary key on a table; this establishes uniqueness among the keys and creates a constraint to enforce these rules. The following example creates a table and loads it with data, which will be used so various strategies for uniqueness can be tested. We begin:


SQL> create table pk_nnui_test
  2  as
  3  with data_source as (
  4  	     select  rownum  p_id
  5  	     from	     dual
  6  	     connect by
  7  			     rownum <= 1000
  8  )
  9  select
 10  	     rownum				     p_id,
 11  	     trunc((rownum-1)/50)		     clust,
 12  	     mod(rownum,20000)			     scatt,
 13  	     lpad(rownum,10)			     strg_small,
 14  	     rpad('x',100,'x')			     strg_padded
 15  from
 16  	     data_source       ds1,
 17  	     data_source       ds2
 18  ;

Table created.

SQL> 
SQL> execute dbms_stats.gather_table_stats(user,'pk_nnui_test',method_opt=>'for all columns size 1')

PL/SQL procedure successfully completed.

SQL> 
SQL> alter system flush buffer_cache;

System altered.

SQL> 

Now that a table exists that contains unique data let’s ensure that doesn’t change by creating a primary key:


SQL> alter table pk_nnui_test add constraint pk_nnui_test_pk primary key(p_id, scatt);

Table altered.

SQL> 
SQL> desc  pk_nnui_test
 Name                                                                     Null?    Type
 ------------------------------------------------------------------------ -------- -------------------------------------------------
 P_ID                                                                     NOT NULL NUMBER
 CLUST                                                                             NUMBER
 SCATT                                                                    NOT NULL NUMBER
 STRG_SMALL                                                                        VARCHAR2(40)
 STRG_PADDED                                                                       VARCHAR2(100)

SQL> 
SQL> select owner, table_name, constraint_name, constraint_type
  2  from user_constraints;

OWNER                          TABLE_NAME                     CONSTRAINT_NAME                C                                      
------------------------------ ------------------------------ ------------------------------ -                                      
BING                           PK_NNUI_TEST                   PK_NNUI_TEST_PK                P                                      
BING                           EMP                            SYS_C004197                    C                                      
BING                           EMP2                           SYS_C004211                    C                                      

SQL> 
SQL> select table_name, index_name
  2  from user_indexes;

TABLE_NAME                     INDEX_NAME                                                                                           
------------------------------ ------------------------------                                                                       
LOB_IDX_TST                    SYS_IL0000072227C00003$$                                                                             
LOB_IDX_TST                    LOB_DAT_IDX                                                                                          
PK_NNUI_TEST                   PK_NNUI_TEST_PK                                                                                      
T1                             IX1                                                                                                  

SQL> 

The key creates an associated constraint and an index, to ensure no duplicate keys can be stored. It isn’t the index many canned applications are searching for, it’s the constraint and this method provides all that such applications need to ‘feel good’ about the data. Keep that in mind as the example proceeds.

Before going any further the primary key constraint is dropped:


SQL> alter table pk_nnui_test drop constraint pk_nnui_test_pk;

Table altered.

SQL> 
SQL> desc pk_nnui_test
 Name                                                                     Null?    Type
 ------------------------------------------------------------------------ -------- -------------------------------------------------
 P_ID                                                                              NUMBER
 CLUST                                                                             NUMBER
 SCATT                                                                             NUMBER
 STRG_SMALL                                                                        VARCHAR2(40)
 STRG_PADDED                                                                       VARCHAR2(100)

SQL> 
SQL> select table_name, index_name
  2  from user_indexes;

TABLE_NAME                     INDEX_NAME                                                                                           
------------------------------ ------------------------------                                                                       
LOB_IDX_TST                    SYS_IL0000072227C00003$$                                                                             
LOB_IDX_TST                    LOB_DAT_IDX                                                                                          
T1                             IX1                                                                                                  

SQL> 

In an attempt to replace the primary key a decision was made to use a combination of unique index and NOT NULL constraints on the indexed columns:


SQL> create unique index pk_nnui_test_unq on pk_nnui_test(p_id, scatt);

Index created.

SQL> 
SQL> alter table pk_nnui_test modify p_id not null;

Table altered.

SQL> alter table pk_nnui_test modify scatt not null;

Table altered.

SQL> 
SQL> desc pk_nnui_test
 Name                                                                     Null?    Type
 ------------------------------------------------------------------------ -------- -------------------------------------------------
 P_ID                                                                     NOT NULL NUMBER
 CLUST                                                                             NUMBER
 SCATT                                                                    NOT NULL NUMBER
 STRG_SMALL                                                                        VARCHAR2(40)
 STRG_PADDED                                                                       VARCHAR2(100)

SQL> 
SQL> select owner, table_name, constraint_name, constraint_type
  2  from user_constraints;

OWNER                          TABLE_NAME                     CONSTRAINT_NAME                C                                      
------------------------------ ------------------------------ ------------------------------ -                                      
BING                           EMP                            SYS_C004197                    C                                      
BING                           EMP2                           SYS_C004211                    C                                      
BING                           PK_NNUI_TEST                   SYS_C004435                    C                                      
BING                           PK_NNUI_TEST                   SYS_C004434                    C                                      

SQL> 
SQL> select table_name, index_name
  2  from user_indexes;

TABLE_NAME                     INDEX_NAME                                                                                           
------------------------------ ------------------------------                                                                       
LOB_IDX_TST                    SYS_IL0000072227C00003$$                                                                             
LOB_IDX_TST                    LOB_DAT_IDX                                                                                          
PK_NNUI_TEST                   PK_NNUI_TEST_UNQ                                                                                     
T1                             IX1                                                                                                  

SQL> 

Constraints exist but only for the NOT NULL conditions set on the indexed columns; no constraint ensuring uniqueness exists. Going back to our third-party application it may not find any indication of enforced uniqueness since there isn’t a unique or primary key constraint in existence. Yes, this method will ensure unique keys but third-party applications may throw an error complaining that no primary key or unique constraint exists.

To remedy this omission the unique index is dropped and a unique constraint is created in its place:


SQL> drop index pk_nnui_test_unq;

Index dropped.

SQL> 
SQL> alter table pk_nnui_test add constraint pk_nnui_test_uq unique(p_id, scatt);

Table altered.

SQL> 
SQL> select owner, table_name, constraint_name, constraint_type
  2  from user_constraints;

OWNER                          TABLE_NAME                     CONSTRAINT_NAME                C                                      
------------------------------ ------------------------------ ------------------------------ -                                      
BING                           PK_NNUI_TEST                   PK_NNUI_TEST_UQ                U                                      
BING                           EMP                            SYS_C004197                    C                                      
BING                           EMP2                           SYS_C004211                    C                                      
BING                           PK_NNUI_TEST                   SYS_C004435                    C                                      
BING                           PK_NNUI_TEST                   SYS_C004434                    C                                      

SQL> 
SQL> select table_name, index_name
  2  from user_indexes;

TABLE_NAME                     INDEX_NAME                                                                                           
------------------------------ ------------------------------                                                                       
LOB_IDX_TST                    SYS_IL0000072227C00003$$                                                                             
LOB_IDX_TST                    LOB_DAT_IDX                                                                                          
PK_NNUI_TEST                   PK_NNUI_TEST_UQ                                                                                      
T1                             IX1                                                                                                  

SQL> 

Now a unique constraint exists for the table in question; the third-party application is happy and all is again right with the world. Along with the unique constraint a unique index is created, as evidenced by checking the data dictionary for such objects. Of course if the constraint is dropped for any reason the index also ‘goes away’:


SQL> alter table pk_nnui_test drop constraint pk_nnui_test_uq;

Table altered.

SQL> 
SQL> select table_name, index_name
  2  from user_indexes;

TABLE_NAME                     INDEX_NAME                                                                                           
------------------------------ ------------------------------                                                                       
LOB_IDX_TST                    SYS_IL0000072227C00003$$                                                                             
LOB_IDX_TST                    LOB_DAT_IDX                                                                                          
T1                             IX1                                                                                                  

SQL> 

This can be remedied by creating a non-unique index on the table (which presumes there is no duplicate data in existence) then creating the unique constraint using that non-unique index:


SQL> create index pk_nnui_test_idx on pk_nnui_test(p_id,scatt);

Index created.

SQL> 
SQL> alter table pk_nnui_test add constraint pk_nnui_test_uq unique(p_id, scatt) using index;

Table altered.

SQL> 
SQL> select owner, table_name, constraint_name, constraint_type
  2  from user_constraints;

OWNER                          TABLE_NAME                     CONSTRAINT_NAME                C                                      
------------------------------ ------------------------------ ------------------------------ -                                      
BING                           PK_NNUI_TEST                   PK_NNUI_TEST_UQ                U                                      
BING                           EMP                            SYS_C004197                    C                                      
BING                           EMP2                           SYS_C004211                    C                                      
BING                           PK_NNUI_TEST                   SYS_C004435                    C                                      
BING                           PK_NNUI_TEST                   SYS_C004434                    C                                      

SQL> 
SQL> select table_name, index_name
  2  from user_indexes;

TABLE_NAME                     INDEX_NAME                                                                                           
------------------------------ ------------------------------                                                                       
LOB_IDX_TST                    SYS_IL0000072227C00003$$                                                                             
LOB_IDX_TST                    LOB_DAT_IDX                                                                                          
PK_NNUI_TEST                   PK_NNUI_TEST_IDX                                                                                     
T1                             IX1                                                                                                  

SQL> 

Now if the constraint is dropped the columns remain indexed, which can preserve application performance since index lookups are usually much faster than full table scans. It isn’t likely that a unique constraint will be dropped since the application may throw an error because no primary key or unique constraint exists. Applications can change, however, and newer versions may no longer need such a constraint. Having the unique constraint use a non-unique index will prevent the loss of that index if the constraint ‘goes away’.

Most applications create their own schemas and constraints so the condition of ‘missing constraints’ may not occur. Remember that the application installation is only as good as the preparation that goes into it, and ‘missing’ tablespaces can throw a wrench into the works, resulting in missing indexes and constraints that may not be apparent until the application is put to use. This is why such applications traipse through the data dictionary to search for required constraints and complain when they don’t exist. Most of these applications won’t check if all of the ‘required’ tablespaces are present, so replacing a unique or primary key constraint can use existing tablespaces without issue; the constraints not only have to exist but must also be named according to the vendor’s specifications. So fixing configuration errors can involve creating the ‘correct’ tablespace or editing the supplied scripts to use an existing tablespace. The vendors prefer the first option and, since you bought the application, you really should make the vendor happy. I have seen a few application installations where tablespaces can be selected from those you have available to make things easier on the customer (you). Be that as it may the key is to have the required constraints in place, properly named so the application can find them.

If unique records are required for an application it’s good to know that creating primary key or unique constraints are the only viable options for most third-party products. It’s also good to know that uniqueness can be enforced without creating a primary key or unique constraint; remember that such an index can be dropped and no one would know that it’s missing until data integrity issues or performance problems rear their ugly heads. Indexes used to enforce unique and primary key constraints cannot be dropped while the constraints are in force:


SQL> drop index pk_nnui_test_idx;
drop index pk_nnui_test_idx
           *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 6
ORA-02429: cannot drop index used for enforcement of unique/primary key


SQL>

Ensuring unique keys isn’t difficult in Oracle; it’s probably best to do it with the proper constraint to keep third-party applications happy.

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