Re: retrieve foreign keys list

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sat, 21 Jul 2001 21:52:35 GMT
Message-ID: <0b5iitoqe3jlacjgc0ptrldsg16jfjbgj3@4ax.com>

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
Howtos and such: http://asktom.oracle.com/ http://asktom.oracle.com/~tkyte/
Oracle Magazine: http://www.oracle.com/oramag

Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  

Opinions are mine and do not necessarily reflect those of Oracle Corp 

Received on Sat Jul 21 2001 - 16:52:35 CDT