p_file_20: unique constraint (...Z308_ID) violated

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

Description:
Error in running p_file_20 (the PLIF loader):

Oracle error: io_z308_write
ORA-00001: unique constraint (USR00.Z308_ID) violated

Or the same for z303.

Resolution:
The z308_rec_key (or z303_rec_key) needs to be unique. This message indicates that p_file_20 was trying to create a z308 (or z303) with a key which is already in use. p_file_20 matches on the USER-REC-MATCH-ID (usually the university ID/ssn). If a barcode (or other ID) in the PLIF record already exists for another user, p_file_20 detects this condition and issues an error message, such as "Z308 Not inserted. 01 type login for <name> already exists for another user".

The Oracle z308 unique constraint error occurs when p_file_20 has *generated* an ID -- which it expects to be unique -- but it is not. There are two cases where p_file_20 generates an ID: (1) it *always* generates a Z303 / Z308 type "00" ALEPH user number; (2) if there is no ID type "01" (barcode) in the input file -- or if its ID-NO field is blank -- p_file_20 will generate Z308 type "01" barcode.

The value for the ALEPH user number comes from the util g/2 last-bor-id counter for the ADM library. The value for the barcode (if not in the input record) comes from the util g/2 last-bor-id-1 counter for the ADM library. Thus, this error indicates that one these counters is giving a value which duplicates an existing value.

In a single ADM situation, one can use the following SQL to locate the highest value for the Z308: SQL> select max(z308_rec_key) from z308 where z308_rec_key like '01%'; <"01" would be for the barcode; "00" for the ALEPH user ID>

The z308 is in the $usr_library. But with a multi-ADM situation, the last-bor-id/last-bor-id-1 counters in the various ADM libraries need to be synchronized with each other to make sure that their values don't overlap. There are two ways to do this: (1) specify a different prefix for each ADM; or (2) specify a different starting key number for each ADM which will not collide with the others.

In addition, the message can occur due to lowercase characters in the ID. See article link in Additional Information below.