Re: How can I get a list of referenced keys?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 12 Jun 1998 23:44:21 GMT
Message-ID: <3583bcc5.2054554@192.86.155.100>

A copy of this was sent to haven_at_havenskys.com (if that email address didn't require changing) On Fri, 12 Jun 1998 20:37:51 GMT, you wrote:

>You can find the information in the SYS.DBA_CONSTRAINTS view.
>- haven - wrote in message <35818C6E.FDF1F7F7_at_havenskys.com>...
>>I need to get drop this table. How can I get a list of referenced keys?

If you just need to drop the table,

SQL> drop table ABC cascade constraints;

will drop it. If you want to drop it, rebuild it, and have the foreign keys put back on, then the following script will help perhaps. You run it on the table you want to drop and it will generate all of the ALTER commands for the children tables to put the foreign keys back on. So, it will list all of the names of the foreign keys referencing the table...

column fkey format a80 word_wrapped
select

'alter table "' || child_tname || '"' || chr(10) ||
'add constraint "' || child_cons_name || '"' || chr(10) ||
'foreign key ( ' || child_columns || ' ) ' || chr(10) ||
'references "' || parent_tname || '" ( ' || parent_columns || ');' fkey

from
( select a.table_name child_tname, a.constraint_name child_cons_name,

         b.r_constraint_name parent_cons_name,
         max(decode(position, 1,     '"'||column_name||'"',NULL)) ||
         max(decode(position, 2,', '||'"'||column_name||'"',NULL)) ||
         max(decode(position, 3,', '||'"'||column_name||'"',NULL)) ||
         max(decode(position, 4,', '||'"'||column_name||'"',NULL)) ||
         max(decode(position, 5,', '||'"'||column_name||'"',NULL)) ||
         max(decode(position, 6,', '||'"'||column_name||'"',NULL)) ||
         max(decode(position, 7,', '||'"'||column_name||'"',NULL)) ||
         max(decode(position, 8,', '||'"'||column_name||'"',NULL)) ||
         max(decode(position, 9,', '||'"'||column_name||'"',NULL)) ||
         max(decode(position,10,', '||'"'||column_name||'"',NULL)) ||
         max(decode(position,11,', '||'"'||column_name||'"',NULL)) ||
         max(decode(position,12,', '||'"'||column_name||'"',NULL)) ||
         max(decode(position,13,', '||'"'||column_name||'"',NULL)) ||
         max(decode(position,14,', '||'"'||column_name||'"',NULL)) ||
         max(decode(position,15,', '||'"'||column_name||'"',NULL)) ||
         max(decode(position,16,', '||'"'||column_name||'"',NULL))
            child_columns

    from user_cons_columns a, user_constraints b    where a.constraint_name = b.constraint_name      and b.constraint_type = 'R'
   group by a.table_name, a.constraint_name, b.r_constraint_name ) child, ( select a.constraint_name parent_cons_name, a.table_name parent_tname,

         max(decode(position, 1,     '"'||column_name||'"',NULL)) ||
         max(decode(position, 2,', '||'"'||column_name||'"',NULL)) ||
         max(decode(position, 3,', '||'"'||column_name||'"',NULL)) ||
         max(decode(position, 4,', '||'"'||column_name||'"',NULL)) ||
         max(decode(position, 5,', '||'"'||column_name||'"',NULL)) ||
         max(decode(position, 6,', '||'"'||column_name||'"',NULL)) ||
         max(decode(position, 7,', '||'"'||column_name||'"',NULL)) ||
         max(decode(position, 8,', '||'"'||column_name||'"',NULL)) ||
         max(decode(position, 9,', '||'"'||column_name||'"',NULL)) ||
         max(decode(position,10,', '||'"'||column_name||'"',NULL)) ||
         max(decode(position,11,', '||'"'||column_name||'"',NULL)) ||
         max(decode(position,12,', '||'"'||column_name||'"',NULL)) ||
         max(decode(position,13,', '||'"'||column_name||'"',NULL)) ||
         max(decode(position,14,', '||'"'||column_name||'"',NULL)) ||
         max(decode(position,15,', '||'"'||column_name||'"',NULL)) ||
         max(decode(position,16,', '||'"'||column_name||'"',NULL))
            parent_columns

    from user_cons_columns a, user_constraints b    where a.constraint_name = b.constraint_name      and b.constraint_type in ( 'P', 'U' )    group by a.table_name, a.constraint_name ) parent where child.parent_cons_name = parent.parent_cons_name   and parent.parent_tname = upper('&1') /  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA  

http://govt.us.oracle.com/ -- downloadable utilities  

---

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

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Fri Jun 12 1998 - 18:44:21 CDT