Re: Foreign Key Constraints: Reverse Engineering

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1998/01/28
Message-ID: <34cf3a59.3898055@192.86.155.100>#1/1

On 27 Jan 1998 17:43:20 GMT, "Harjit S. Batra" <hbatra_at_NOSPAM_cciosi.com> wrote:

>Does anybody know of a way to build a PL-SQL script from an existing
>database. I need a way to build a script that will contain ONLY the
>definitions of all the Foreign Key constraints that exist in a current
>database. I am using Oracle 7.3.
>
>An e-mail direct to me in addition to the NG post will be greatly
>appreciated !
>
>TIA
The following query will extract the foreign keys given a single parent table, for example if you save the script to a file called cons.sql you can then:

SQL> create table p ( x int primary key ); Table created.

SQL> create table c ( a int primary key, b int references p ); Table created.

SQL> @cons p
old 53: and parent.parent_tname = upper('&1') new 53: and parent.parent_tname = upper('p')

FKEY

---

alter table "C"
add constraint "SYS_C008536"
foreign key ( "B" )
references "P" ( "X");

---

So, the query should get you going...

• cons.sql ------------------------------------------

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')
/

• eof ----------------------------------------------

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD  

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 Wed Jan 28 1998 - 00:00:00 CST