Forum OpenACS Q&A: Response to How do you find foreign keys in Oracle?

Collapse
Posted by Michael Bryzek on
Here's a small oracle package with a few procedures to print out random info... After loading the package, you can run the following command to get the tables (and constraint names) for tables that reference your table... Hope it helps.
  set serveroutput on
  exec bryzek_util.references_to_table('FOO');
You'll get a list of all the tables and constraint names that reference FOO. You can then look at the SQL for references_to_table and should be able to quickly modify it if you need more info.

Sample output:


18:25:29 bryzek@ora8i> create table foo (id integer primary key);

Table created.

18:25:30 bryzek@ora8i> create table bar (bar_id references foo(id));

Table created.

18:25:37 bryzek@ora8i> exec bryzek_util.references_to_table('BAR');
References to BAR
- No references found

PL/SQL procedure successfully completed.

18:25:42 bryzek@ora8i> exec bryzek_util.references_to_table('FOO');
References to FOO
- BAR (SYS_C0044410)

PL/SQL procedure successfully completed.

18:25:45 bryzek@ora8i> 

Oracle package:

-- bryzek-util.pls
-- 
-- Random oracle utilities
-- @author Michael Bryzek
-- @id $Id: bryzek-util.pls,v 1.1.1.1 2002/01/29 17:58:24 mbryzek Exp $
--
-- Copyright (C) 2001 Michael Bryzek
-- 
-- This program is free software; you can redistribute it and/or
-- modify it under the terms of the GNU General Public License
-- as published by the Free Software Foundation; either version 2
-- of the License, or (at your option) any later version.
-- 
-- This program is distributed in the hope that it will be useful,
-- but WITHOUT ANY WARRANTY; without even the implied warranty of
-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
-- GNU General Public License for more details.
-- 
-- You should have received a copy of the GNU General Public License
-- along with this program; if not, write to the Free Software
-- Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.
-- 

create or replace package bryzek_util AUTHID CURRENT_USER as

  /**
   * Disables all the constraints on the specified table, including
   * the referential constraints from tables that reference this one.
   * 
   * @author Michael Bryzek
   * @creation-date 2001-08-06
   **/
  procedure disable_constraints (
        p_table_name user_tables.table_name%TYPE
  );

  /**
   * Enables all the constraints on the specified table, including
   * the referential constraints from tables that reference this one.
   * 
   * @author Michael Bryzek
   * @creation-date 2001-08-06
   **/
  procedure enable_constraints (
        p_table_name user_tables.table_name%TYPE
  );

  /**
   * Drops the specified table only if it exists.
   * 
   * @author Michael Bryzek
   * @creation-date 2001-08-06
   **/
  procedure drop_table (
        p_table_name user_tables.table_name%TYPE
  );

  /**
   * Displays all tables that reference the specified table.
   * 
   * @author Michael Bryzek
   * @creation-date 2001-08-21
   **/
  procedure references_to_table (
        p_table_name user_tables.table_name%TYPE
  );


  /**
   * Returns 1 if a table with the specified name exists
   * (case-insensitive). Returns 0 otherwise
   * 
   * @author Michael Bryzek
   * @creation-date 2001-08-21
   **/
  function table_exists (
        p_table_name user_tables.table_name%TYPE
  ) return integer;


  /**
   * Attempts to drop the user schema by first dropping all
   * constraints, then dropping all objects from the
   * user_objects table.
   * 
   * @author Michael Bryzek
   * @creation-date 2001-09-21
   **/
  procedure drop_user_schema;


  /**
   * Drops all constraints in the current user's schema.
   * 
   * @author Michael Bryzek
   * @creation-date 2001-09-21
   **/
  procedure drop_user_constraints;

end bryzek_util;
/
show errors;


create or replace package body bryzek_util as
  --------------------------------------------------------------------
  -- PRIVATE METHODS
  --------------------------------------------------------------------

  -- Returns 1 if an object with the specified name and type exists
  function object_exists (
        p_object_type user_objects.object_type%TYPE,
        p_object_name  user_objects.object_name%TYPE
  ) 
  return integer
  is
    v_result integer;
  begin
    select count(*) into v_result
      from user_objects
     where object_name = upper(trim(p_object_name))
       and object_type = upper(trim(p_object_type));
    
    return v_result;
  end;

  procedure output (
        p_message VARCHAR,
        p_sql     VARCHAR
  )
  is
     p_display integer := 0;
  begin 
     if (p_display = 1) then
       dbms_output.put_line('----------------------------------------------------------------------');
       dbms_output.put_line(p_message);
       dbms_output.put_line(p_sql);
     end if;
  end;

  procedure drop_constraint (p_table           user_tables.table_name%TYPE,
                             p_constraint_name user_constraints.constraint_name%TYPE) 
  is
    v_sql varchar(4000);
  begin
    begin 
        v_sql := 'alter table ' || p_table || ' drop constraint ' || p_constraint_name;
        dbms_output.put_line(v_sql);
        execute immediate v_sql;
    exception when others then
          dbms_output.put_line('Error dropping constraint ' || p_constraint_name || ': ' || SUBSTR(SQLERRM,1,100));
    end;
  end;

  --------------------------------------------------------------------
  -- PUBLIC API
  --------------------------------------------------------------------

  procedure drop_table (
        p_table_name user_tables.table_name%TYPE
  )
  is
     v_result integer := 0;
  begin
     execute immediate 'select count(*) ' ||
                         'from user_tables ' ||
                        'where table_name=upper(trim(:table_name))' into v_result using p_table_name;
     if (v_result > 0) then 
        execute immediate 'drop table ' || p_table_name;
     end if;

  end;

  procedure constraint_toggle_foreign (
        p_status     user_constraints.status%TYPE,
        p_table_name user_tables.table_name%TYPE
  ) 
  is
     TYPE row_cursor_type IS REF CURSOR;
     row_cursor row_cursor_type;

     v_sql varchar(4000);

     v_table_name       user_tables.table_name%TYPE;
     v_constraint_name  user_constraints.constraint_name%TYPE;
     v_constraint_type  user_constraints.constraint_type%TYPE := 'R';
  begin

     -- query to pull out constraints from tables that reference this one
     v_sql := 'select constraint_name, table_name ' ||
                'from user_constraints ' ||
               'where constraint_type=:1 ' ||
                 'and status not like ''' || p_status || '%'' ' ||
                 'and r_constraint_name in (select constraint_name ' ||
                                             'from user_constraints  ' ||
                                            'where constraint_type in (''P'',''U'')  ' ||
                                              'and table_name=upper(trim(:2)))';
     
     output('Retrieving foreign key constraints to table', v_sql);
     open row_cursor for v_sql using v_constraint_type, p_table_name;
     LOOP 
       fetch row_cursor into v_constraint_name, v_table_name;
       exit when row_cursor%NOTFOUND;
       v_sql := 'alter table ' || v_table_name || ' ' || p_status || ' constraint ' || v_constraint_name;
       output('Query to ' || p_status || ' this constraint', v_sql);
       execute immediate v_sql;
     end loop;
  end;

  procedure constraint_toggle_table (
        p_status     user_constraints.status%TYPE,
        p_table_name user_tables.table_name%TYPE
  ) 
  is
     TYPE row_cursor_type IS REF CURSOR;
     row_cursor row_cursor_type;

     v_sql varchar(4000);

     v_table_name       user_tables.table_name%TYPE;
     v_constraint_name  user_constraints.constraint_name%TYPE;
     v_constraint_type  user_constraints.constraint_type%TYPE := 'R';
  begin

     -- query to pull out remaining enabled constraints on our table
     -- This actually doesn't need dynamic sql, but i wanted to learn
     -- how to declare my own row cursor
     v_sql := 'select constraint_name, table_name ' ||
                'from user_constraints ' ||
               'where status not like ''' || p_status || '%'' ' ||
                 'and table_name=upper(trim(:1))';
     
     output('Retrieving remaining enabled constraints on table', v_sql);

     open row_cursor for v_sql using p_table_name;
     LOOP 
       fetch row_cursor into v_constraint_name, v_table_name;
       exit when row_cursor%NOTFOUND;
       v_sql := 'alter table ' || v_table_name || ' ' || p_status || ' constraint ' || v_constraint_name;
       output('Query to ' || p_status || ' this constraint', v_sql);
       execute immediate v_sql;
     end loop;

  end;


  procedure disable_constraints (
        p_table_name user_tables.table_name%TYPE
  )
  is
  begin
    constraint_toggle_foreign('DISABLE', p_table_name);
    constraint_toggle_table('DISABLE', p_table_name);
  end;

  procedure enable_constraints (
        p_table_name user_tables.table_name%TYPE
  )
  is
  begin
    constraint_toggle_table('ENABLE', p_table_name);
    constraint_toggle_foreign('ENABLE', p_table_name);
  end;

  procedure references_to_table (
        p_table_name user_tables.table_name%TYPE
  )
  is
     v_found INTEGER := 0;
  begin
     if table_exists(p_table_name) = 0 then
        raise_application_error(-20000, 'There is no table named: ' || p_table_name);
     end if;
     dbms_output.put_line('References to ' || p_table_name);
     for row in (select constraint_name, table_name
                   from user_constraints 
                  where r_constraint_name in (select constraint_name
                                                from user_constraints
                                               where table_name = upper(trim(p_table_name)))) loop
        dbms_output.put_line('  - ' || row.table_name || ' (' || row.constraint_name || ')');
        if v_found = 0 then 
           v_found := 1;
        end if;
     end loop;

     if v_found = 0 then
        dbms_output.put_line('  - No references found');
     end if;
  end;

  function table_exists (
        p_table_name user_tables.table_name%TYPE
  ) 
  return integer
  is
  begin
    return object_exists('table', p_table_name);
  end;

  procedure drop_user_constraints
  is
    v_sql    varchar(4000);
    v_exists integer;
  begin
    for row in (select cols.table_name, con.constraint_name
                  from user_constraints con, user_cons_columns cols
                 where con.constraint_name = cols.constraint_name
                   and con.constraint_type = 'R') loop
        drop_constraint(row.table_name, row.constraint_name);
    end loop;

    for row in (select cols.table_name, con.constraint_name
                  from user_constraints con, user_cons_columns cols, user_tables tabs
                 where con.constraint_name = cols.constraint_name
                   and cols.table_name = tabs.table_name
                   and (tabs.iot_name is null or con.constraint_type <> 'P')) loop
        drop_constraint(row.table_name, row.constraint_name);
   end loop;
  end;


  procedure drop_user_schema
  is
    v_sql    varchar(4000);
    v_exists integer;
  begin
    drop_user_constraints;

    for row in (select object_type, object_name
                  from user_objects 
                 where object_type in ('PACKAGE', 'VIEW', 'TABLE', 'SEQUENCE')
                   and (object_name <> 'BRYZEK_UTIL')) loop
        v_sql := 'drop ' || row.object_type || ' ' || row.object_name;
        dbms_output.put_line(v_sql);
        execute immediate v_sql;
    end loop;
  end;

end bryzek_util;
/
show errors;