Re: Foreign Key Constraints: Reverse Engineering
Date: 1998/01/28
Message-ID: <34cf3a59.3898055_at_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> _at_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 || ');' fkeyfrom
( 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 CET