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.
