Skip to main content
ExLibris
  • Subscribe by RSS
  • Ex Libris Knowledge Center

    manage-27: "cannot CREATE UNIQUE INDEX; duplicate keys found"

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

    Problem Symptoms:
    The manage-27 job ("Update Sort Index") runs to completion, but:

    * the $alephe_scratch/p_manage_27 log has this message near the end:

    Error : Only 0 indexes out of 1 for table z101 were created

    * the xxx01 $data_scratch/create_ora_tables_z101_id.log has the following errors:

    CREATE UNIQUE INDEX xxx01.Z101_ID ON BRO01.Z101 (Z101_REC_KEY ASC)
    *
    ERROR at line 1:
    ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

    * util a/17/14 for z101 shows that the z101_id Oracle index does not exist

    Cause:
    This SQL shows that there is just one duplicate key:

    sys60@ALEPH1> select z101_rec_key from z101 group by z101_rec_key having count(* ) > 1;
    **** Hit return to continue ****

    Z101_REC_KE
    -----------
    0

    And the following that the two records having this key are bogus and extraneous (all other columns blank):

    sys60@ALEPH1> select * from z101 where z101_rec_key in (select z101_rec_key from z101 group by z101_rec_key having count(* ) > 1);
    **** Hit return to continue ****

    Z101_REC_KE Z
    ----------- -
    Z101_TEXT
    --------------------------------------------------------------------------------
    0

    0

    See also Cases 2 and 3 described in Additional Information below.

    Resolution:
    Since there are just the two duplicate z101_rec_keys, and they are both bogus, the best thing is to delete them:

    sys60@ALEPH1> delete from z101 where z101_rec_key in (select z101_rec_key from z101 group by z101_rec_key having count(* ) > 1);

    The response should be "Two rows deleted".
    Then do "commit;"

    Then you should be able to do util a/17/2 to create the z101_id Oracle index.

    Note: If the duplicates are not bogus, then util a/17/18 should be run as described in Article 000013990 ("Using util a/17/18") .

    Additional Information

    Case 2: the following SQL showed thousands of duplicate z101_rec_key's of the form "0 " and "00000000 ":

    SQL> select z101_rec_key, count(*) from z101 where z101_text is null group by z101_rec_key;

    Case 3: the above SQL found two duplicate z101_rec_keys:.

    e95 1995 2
    f4 1982 2

    (Note: The first 9 characters in the z101_rec_key *should* be the xxx01 doc number. "2" is Sort type 2; the call number)

    For Case 2: Do the following SQL to confirm that these bib keys don't actually exist:

    SQL> select count(*) from z00 where z00_doc_number = '0 ';
    SQL> select count(*) from z00 where z00_doc_number = '00000000 ';

    Run util a/17/18 as described in Article 13990 to build a unique z101_id.

    Case 3: Locate the defective records using the following SQL:

    SQL> select z30_rec_key, z30_call_number, z30_hol_doc_number_x from z30 where z30_call_no like '%e95%1995%';
    SQL> select z30_rec_key, z30_call_number, z30_hol_doc_number_x from z30 where z30_call_no like '%f4%1982%';

    Bring up the HOL records in GUI Cataloging. Assuming that you see boxes in the call number (indicating an invalid character), delete the boxes (or correct them to what they should be).

    Re-run manage-27.

    Category: Background processing (500)

    Subject: Indexing (500)


    • Article last edited: 11/25/2014