how to find the table name on which integrity constraint not found
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: how to find the table name on which integrity constraint not found

Hybrid View

  1. #1
    Join Date
    Dec 2002
    Posts
    110

    how to find the table name on which integrity constraint not found

    Hi All

    How to acheive this

    I have a lot of tables with lot of primary key - foreign key
    relationship.
    In plsql
    when any inserts happen in the child table & the corresponding row is not present in the parent table, we get an exception

    ORA-02291: integrity constraint (user1.ppk) violated - parent key not found

    On this exception , in the exception block i want to trap teh name of the parent table on which the primary key for the particular child table was not there

    Is it possible to retrieve the parent table in this way. I am looking for a generic plsql code block which can help to acheive this


    Regards

  2. #2
    Join Date
    Nov 2003
    Location
    Ohio
    Posts
    51
    Here's an example. You may want to clean up the string parsing a bit. You shoul put the code in a stored procedure and then call it in any exception handler that requires it.

    scott@AMSAA>
    1 begin
    2 insert into test2 values(-999, 'test');
    3 end;
    4*
    scott@AMSAA> /
    begin
    *
    ERROR at line 1:
    ORA-02291: integrity constraint (SCOTT.TEST2_FK) violated - parent key not found
    ORA-06512: at line 2



    declare

    l_err_tbl user_constraints.table_name%type;
    l_cons user_constraints.constraint_name%type;
    l_start number;
    l_stop number;

    -- user defined exception to handle error we are hitting
    e_ConsViolation exception;
    pragma exception_init(e_ConsViolation, -02291);

    begin

    insert into test2 values(-999, 'test');
    exception

    when e_ConsViolation then

    l_start := instr( sqlerrm, '(' ) + 1;
    l_stop := instr( sqlerrm, ')' );
    l_cons := substr(sqlerrm, l_start, l_stop-l_start);
    l_start := instr( l_cons, '.' ) + 1;
    l_cons := substr(l_cons, l_start, l_stop-l_start);

    select table_name
    into l_err_tbl
    from user_constraints
    where constraint_name= (select r_constraint_name
    from user_constraints
    where constraint_name = l_cons);

    dbms_output.put_line('Parent table is ' || l_err_tbl);

    when others then
    dbms_output.put_line('other section');

    end;
    /

    Parent table is TEST1

    PL/SQL procedure successfully completed.

  3. #3
    Join Date
    Jul 2003
    Posts
    53

    Just try out this

    CREATE OR REPLACE
    PROCEDURE show_fkeys(
    p_table_name IN user_constraints.table_name%TYPE)
    IS
    -- constants
    -- identify fkeys on pkey for given table
    CURSOR id_fkeys (
    c_table_name user_constraints.table_name%TYPE)
    IS
    SELECT table_name, constraint_name fkey, r_constraint_name pkey,
    status
    FROM user_constraints
    WHERE constraint_type='R'
    AND r_constraint_name IN (
    SELECT constraint_name
    FROM user_constraints
    WHERE table_name=c_table_name
    AND constraint_type='P')
    ORDER BY table_name, constraint_name;
    -- record variables
    rec_id_fkeys id_fkeys%ROWTYPE;
    -- variables
    l_table_name user_constraints.table_name%TYPE;
    l_pkey_name user_constraints.constraint_name%TYPE;
    l_status NUMERIC;
    BEGIN
    l_table_name := UPPER(p_table_name);
    -- a primary key for the given table must exist
    SELECT constraint_name
    INTO l_pkey_name
    FROM user_constraints
    WHERE table_name=l_table_name
    AND constraint_type='P';
    DBMS_OUTPUT.put_line(
    'show_fkeys: foreign key constraints on table [' || l_table_name || ']');
    DBMS_OUTPUT.put_line(
    ' whose primary key is [' || l_pkey_name || ']');
    OPEN id_fkeys(l_table_name);
    LOOP -- display foreign keys
    FETCH id_fkeys INTO rec_id_fkeys;
    EXIT WHEN id_fkeys%NOTFOUND;
    DBMS_OUTPUT.put_line(RPAD('Table: [' || rec_id_fkeys.table_name || ']',40) ||
    RPAD('FK Name: [' || rec_id_fkeys.fkey || ']',42) ||
    'Status: [' || rec_id_fkeys.status || ']');
    END LOOP; -- display foreign keys
    IF (id_fkeys%ROWCOUNT = 0) THEN -- no fkeys found
    DBMS_OUTPUT.put_line(
    'show_fkeys: No foreign keys found against table ' || l_table_name);

    END IF; -- no rows found
    CLOSE id_fkeys;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN -- primary key lookup failed
    DBMS_OUTPUT.put_line(
    'show_fkeys: no primary key exists for table ' || l_table_name);
    WHEN OTHERS THEN
    l_status := SQLCODE;
    DBMS_OUTPUT.put_line('show_fkeys: ' || SQLERRM(l_status));
    IF (id_fkeys%ISOPEN) THEN
    CLOSE id_fkeys;
    END IF;
    END show_fkeys;
    /
    anu

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width