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

    Unique constraint (XXX50.Z305_ID) violated

    1. Last updated
    2. Save as PDF
    • Article Type: General
    • Product: Aleph
    • Product Version: 20

    Problem Symptoms:
    Running SQL to change sublibrary of certain XXX50 Z305 (PATRON?€™S LOCAL INFORMATION) records from 'YYYYY' to ' ZZZZZ':

    UPDATE Z305
    SET Z305_REC_KEY = substr(Z305_REC_KEY,1,12) || 'ZZZZZ'
    where substr(Z305_REC_KEY,13,5) = 'YYYYY'

    results in the following message:

    Error:
    *
    ERROR at line 1:
    ORA-00001: unique constraint (XXX50.Z305_ID) violated

    Cause:
    SQL didn't include check to see if a "ZZZZZ" record already existed for the patron.

    Resolution:
    Add check to see if a "ZZZZZ" record already exists for patron:

    UPDATE Z305
    SET Z305_REC_KEY = substr(Z305_REC_KEY,1,12) || 'ZZZZZ'
    where substr(Z305_REC_KEY,13,5) = 'YYYYY'
    and substr(Z305_REC_KEY,1,12) not in
    (select substr (Z305_REC_KEY,1,12) from z305 where substr (Z305_REC_KEY,13,5) = 'ZZZZZ');

    Category: System Management (500)

    Subject: Oracle (500)

    • Article last edited: 10/8/2013