rem 
rem Description   This SQL script generates referential constraints 
rem               for a table.  (Not including primary key info) 
rem 
rem Argument(s)   Spool File Name, Table name 
rem 

  
set FEEDBACK OFF 
set ECHO OFF 
set VERIFY OFF 
set LINESIZE 160
COLUMN DUMMY_1 NOPRINT FORMAT A30 
COLUMN DUMMY_2 NOPRINT FORMAT 9 
COLUMN DUMMY_3 NOPRINT FORMAT 99 
COLUMN COMMAND FORMAT A160
  
 
SELECT 'ALTER TABLE '||C.TABLE_NAME||' ADD CONSTRAINT '|| 
       C.CONSTRAINT_NAME||' FOREIGN KEY (' COMMAND, 
       C.CONSTRAINT_NAME DUMMY_1, 1 DUMMY_2, 0 DUMMY_3 
  FROM SYS.DBA_CONSTRAINTS C,SYS.DBA_INDEXES I 
 WHERE I.TABLE_NAME = UPPER('&&TABLE_NAME') 
       AND C.R_CONSTRAINT_NAME = I.INDEX_NAME 
       AND C.CONSTRAINT_NAME IN (SELECT CONSTRAINT_NAME 
  FROM SYS.DBA_CONSTRAINTS
 WHERE CONSTRAINT_TYPE = 'R') 
       UNION 
SELECT DECODE(CC.POSITION,1,NULL,',') || CC.COLUMN_NAME COMMAND, 
       CC.CONSTRAINT_NAME DUMMY_1, 2 DUMMY_2, CC.POSITION DUMMY_3 
  FROM SYS.DBA_CONS_COLUMNS CC,SYS.DBA_CONSTRAINTS C, SYS.DBA_INDEXES I 
 WHERE I.TABLE_NAME = UPPER('&&TABLE_NAME') 
       AND C.R_CONSTRAINT_NAME = I.INDEX_NAME 
       AND CC.CONSTRAINT_NAME = C.CONSTRAINT_NAME 
       AND CC.CONSTRAINT_NAME IN (SELECT CONSTRAINT_NAME 
  FROM SYS.DBA_CONSTRAINTS 
 WHERE CONSTRAINT_TYPE = 'R') 
       UNION 
SELECT ') REFERENCES '||I.TABLE_NAME||' (' COMMAND, 
       C.CONSTRAINT_NAME DUMMY_1, 3 DUMMY_2, 0 DUMMY_3 
  FROM SYS.DBA_INDEXES I, SYS.DBA_CONSTRAINTS C 
 WHERE I.TABLE_NAME = UPPER('&&TABLE_NAME') 
       AND C.R_CONSTRAINT_NAME = I.INDEX_NAME 
       AND CONSTRAINT_NAME IN (SELECT CONSTRAINT_NAME  
  FROM SYS.DBA_CONSTRAINTS 
 WHERE CONSTRAINT_TYPE = 'R') 
       UNION 
SELECT DECODE(IC.COLUMN_POSITION,1,NULL,',') ||  
       IC.COLUMN_NAME COMMAND, 
       C.CONSTRAINT_NAME DUMMY_1, 4 DUMMY_2, 
       IC.COLUMN_POSITION DUMMY_3 
  FROM SYS.DBA_IND_COLUMNS IC,  
       SYS.DBA_CONSTRAINTS C,SYS.DBA_INDEXES I 
 WHERE I.TABLE_NAME = UPPER('&&TABLE_NAME') 
       AND C.R_CONSTRAINT_NAME = I.INDEX_NAME 
       AND IC.INDEX_NAME = I.INDEX_NAME 
       AND C.CONSTRAINT_NAME IN (SELECT CONSTRAINT_NAME  
  FROM SYS.DBA_CONSTRAINTS 
 WHERE CONSTRAINT_TYPE = 'R') 
       UNION 
SELECT ')'||DECODE(C.DELETE_RULE,'CASCADE', 
       ' ON DELETE CASCADE',NULL)|| 
       DECODE(C.STATUS,'DISABLED',' DISABLE',NULL)||';' COMMAND, 
       C.CONSTRAINT_NAME DUMMY_1 ,5 DUMMY_2, 0 DUMMY_3 
  FROM SYS.DBA_CONSTRAINTS C, SYS.DBA_INDEXES I 
 WHERE I.TABLE_NAME = UPPER('&&TABLE_NAME') 
       AND C.R_CONSTRAINT_NAME = I.INDEX_NAME 
       AND CONSTRAINT_NAME IN (SELECT CONSTRAINT_NAME  
  FROM SYS.DBA_CONSTRAINTS 
 WHERE CONSTRAINT_TYPE = 'R') 
       ORDER BY 2,3,4;