Re: Reorgs

From: Robert S. Bunting <rbunting_at_MAIL.COIN.MISSOURI.EDU>
Date: Mon, 8 Jan 1996 12:00:34 -0600
Message-Id: <9601081804.AA16515@alice.jcc.com>

The following script creates foreign keys based on a common field name. You will need to create your primary key first.

/*
filename: make_foreign_keys.sql
author: rbunting
ddl created: 27-Sep-95

remarks:     Creates a file of possible foreign keys for all tables that
             have the same column name.

*/
set linesize 200
set pagesize 100
set heading off
set echo off
set verify off
spool &&COLUMN_foreign_keys.sql
select 'alter table '||table_name||' add constraint '||substr( 'fk_' ||table_name||'_&&column',1,30)||' foreign key ( &&column ) references '|| '&&table ( &&COLUMN );'
from user_tab_columns t, user_objects o

where t.column_name = upper('&&COLUMN')
  and o.object_type = 'TABLE'
  and t.table_name = o.object_name

/
spool off

Robert Bunting
Missouri Employer's Mutual
Columbia, Mo
314-499-4135

On Mon, 8 Jan 1996, Ian wrote:

> Hello all
>
> Can you help me with a neat way to create foreighn key constraints
> from an existing database ( to help with object-by-object
> reorganisations )
>
> .
> i
>
Received on Mon Jan 08 1996 - 13:05:01 CST