Skip to main content
ExLibris
  • Subscribe by RSS
  • ExLibris Dev

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

    • Article Type: General
    • Product: Aleph
    • Product Version: 16.02

    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...

    Additional Information

    faq, oracle, duplicates, unique


    • Article last edited: 12/10/2014
    //doorbell.io feedback widged