Cannot CREATE UNIQUE INDEX; duplicate keys found; using util a/17/18 **HOW TO**

• Article Type: General
• Product: Aleph
• Product Version: 20, 21, 22, 23

Description:
In running a batch indexing job or in running util a/17/2 to build an Oracle index (such as z01_id2, z02_id, z97_id3, z98_id, z0102_id, etc.), you get the following message.
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

Browsing the ABC12 headings through the GUI in prod is slow and not producing expected results, eg Nursing Periodicals produces no results when there are hundreds with that heading. Using util a/17/14 and matching against the file_list I see that z00h_id and z02_id are missing. I tried creating z02_id with util a/17/2 but got a unique constraint error:
Cannot CREATE UNIQUE INDEX; duplicate keys found
Are these missing indexes responsible for the problem? Can their tables be dropped and recreated or is an indexing job like p-manage-02 required?

Resolution:
In the case where util a/17/2 encounters duplicates for an Oracle index which is defined as "Unique", you can use util a/17/18 to build the index instead. This utility:
1. builds a non-unique index
2. locates and deletes the duplicates; and
3. builds a unique index.

**Before running this utility, you need to stop any servers, ue daemons, or batch jobs which might be accessing the table on which it is trying to build the index.**

Notes: The util a/17/18 asks you to enter an index: "enter/1/2/3/4/5 :"
If the index is "id1", you enter "1"; if it is "id2", you enter "2", etc. If -- as is most frequently the case -- the index is just plain "id", with no number, you leave it blank and press Enter.
Then it says "Enter Column Name". To see what column a particular index is built on, go to the util a/17/14 for a library where the index *does* exist, such as the corresponding USMnn library. The last column, the "COLUMN_NAME", is the value which you would enter.
At this point it will pause with: "aleph_admin@ALEPH0> 14:37:34 aleph_admin@ALEPH0> " while it is building the index. Do *NOT* press enter or any key after it starts the index build. If you do, it will be used as input to the "List/Delete" choice which comes up next (see below), and will be interpreted as a choice of the default (List) -- which is almost never what you want....
After building the non-unique index, the utility pauses and gives you a choice of "List" or "Delete". If you choose "List", the duplicates will not be deleted and the unique index will not be built. To do that, you need to specify "Delete".

Note: In the case where the table for which the Oracle index is being built is an ALEPH index table or a temporary table, then can go ahead and do this Delete. But in the case where the table is not an index/temporary table but instead, a "corpus table" (that is, a data table), you should do the "List" instead.

If you are uncertain whether the table is an index table, a temporary table, or a corpus table, please consult KB# 4138.

Below is a sample dialog for util a/17/18:
Enter Table Name : z103 <-----
Enter Index enter/1/2/3/4/5 : <----- Leave blank for znnn_id
index_name z103_id index_suffix
Enter Column Name : Z103_REC_KEY <---------
A NONUNIQUE INDEX z103_id will be created, enter Y to confirm y <-----
USM30 drop_index z103_id
l_op == drop_index
l_table == z103_id
...
...
aleph_admin@ALEPH5> 05:09:11 aleph_admin@ALEPH5>
Index dropped.
...
...
aleph_admin@ALEPH5> 05:09:11 aleph_admin@ALEPH5>
Index created.
...
...
Enter CR to continue...
********************************************************
Enter action to list duplicate keys to file or to delete them
(LIST/DELETE):delete <----------------------
Elapsed: 00:00:00.07
USM30 drop_index z103_id
l_op == drop_index
l_table == z103_id
...
aleph_admin@ALEPH5> 05:09:18 aleph_admin@ALEPH5>
Index dropped.
...
USM30 index z103_id
l_op == index
l_table == z103_id
...
...
aleph_admin@ALEPH5> 05:09:18 aleph_admin@ALEPH5>
Index created.
...
...
Enter CR to continue...

If the index is a Z97_idn index and this is the first time that manage-01 is being run in the library, then consult the article p_manage_01 in xxx60: Z97_ID3; cannot CREATE UNIQUE INDEX; duplicate keys found .