Re: retrieve foreign keys list

From: Bob Wall <wall_at_nospam_montanadsl.net>
Date: Sat, 21 Jul 2001 21:36:43 GMT
Message-ID: <3B4F7FDE.62BDB225@nospam_montanadsl.net>

Thomas Kyte wrote:
>
> A copy of this was sent to "Jean" <ken_jean_at_hotmail.com>
> (if that email address didn't require changing)
> On Thu, 14 Jun 2001 18:28:14 +0200, you wrote:
>
> >Hi to all!!
> >I have an Oracle 8 database.
> >No, I'm implementing an import operation from an ASP page.
> >Because there are some relationships between tables when I try to delete
> >rows before import or I try to insert rows in a table without related rows
> >in the other table I receive an error.
> >So I decide to drop all foreign keys from destination database, import data
> >and then add foreign keys.
> >It is good, but I don't say how I can retrieve foreign keys list for tables.
> >In SQL Server 2000 there is a stored procedure called sp_fkeys where passing
> >table name I have for result a recordset with all foreign keys for specified
> >table.
> >Does exist a similar stored procedure, function or SQL statement that is
> >able to retrieve table's foreign keys?
> >Remember that I need to use this function from ASP so I need a statement
> >that I can use from ADO.
> >
> >Please help me.
> >Thanks in advance.
> >Bye
> >
>
> user_constraints has this info.
>
> Don't DROP the constraints -- just turn them off for a bit. that way you don't
> have to reconstruct the entire constraint command.
>
> Consider:
>
> create table p ( x int primary key );
> create table c1 ( a references p );
> create table c2 ( b references p );
>
> Now, run a script like this:
>
> set heading off
> set feedback off
> set echo off
> spool tmp.sql
> select 'alter table ' || table_name || ' disable constraint ' || constraint_name
> || ';'
> from user_constraints
> where constraint_type = 'R'
> and r_constraint_name = ( select constraint_name
> from user_constraints where constraint_type = 'P'
> and table_name = 'P' )
> /
> spool off
> @tmp
>
> ----------------------------------------
>
> that'll turn off all fkeys pointing to P
>
> now we can load the data backwards:
>
> insert into c1 values ( 1 );
> commit;
> insert into c2 values ( 1 );
> commit;
> insert into p values ( 1 );
> commit;
>
> and then later do this:
>
> set heading off
> set feedback off
> set echo off
> spool tmp.sql
> select 'alter table ' || table_name || ' enable constraint ' || constraint_name
> || ';'
> from user_constraints
> where constraint_type = 'R'
> and r_constraint_name = ( select constraint_name
> from user_constraints where constraint_type = 'P'
> and table_name = 'P' )
> /
> spool off
> set echo on
> @tmp
>
> to turn them all back on.
> --
> Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries

   Or you can do this, instead of spooling to a file:

DECLARE
   CURSOR c1 IS

      SELECT table_name, constraint_name
        FROM user_constraints
       WHERE cosntraint_type = 'R'
         AND r_constraint_name =
	     (SELECT constraint_name FROM user_constraints
               WHERE constraint_type = 'P' AND table_name = 'P' ;

   table_name VARCHAR2(50);
   constraint_name VARCHAR2(50);

BEGIN
   OPEN c1;
   LOOP

      FETCH c1 INTO table_name, constraint_name;
      EXIT WHEN c1%NOTFOUND;
      EXECUTE IMMEDIATE 'ALTER TABLE ' || table_name || ' DISABLE
CONSTRAINT ' ||
                        constraint_name;

   END LOOP;
   CLOSE c1;
END;
/

(At least in Oracle 8.1.6 and 8.1.7.)

Bob Wall Received on Sat Jul 21 2001 - 16:36:43 CDT