recreate all foreign keys

[stmontgo]

hey there,
anyone have a script handy that generates sql to create all the foreign key's in the database? Our client does not like ON DELETE CASCADE to be left on. However, once in a while we need to use that functionlity so I as going to drop the FK's in restricted session, recreate them with ON DELETE CASCADE, perform the operation what requires DELETE CASCADE, and then drop and recreate the FK's without ON DELETE CASCADE. I know this sounds kind of hokey but the ON DELETE CASCADE functionality is only ever required once every odd month so. I know I can get the constraints from import export but I'd like a dynaminc sql that I can just edit one and run.

steve

[stecal]

Good luck with this. Have not used it myself.

http://www.bijoos.com/ora_docs/cr_fk_to_tab.txt

rem
rem Generate Foreign Key Creation DDL for everything pointing to a table
rem
rem Input : Tablename
rem
rem Chris Brown - Bedford, NS Canada
rem
set heading off pagesize 999 feedback off verify off
spool create_fks_to_&1..sql
REM
REM
SELECT 'ALTER TABLE '||fk.table_name||' ADD ('||chr(10)||
' CONSTRAINT '||fk.constraint_name||chr(10)||
' FOREIGN KEY'||chr(10)||
' ('||fkcol1.COLUMN_NAME||
DECODE(fkcol2.COLUMN_NAME, NULL, NULL,chr(10)||' ,'||fkcol2.COLUMN_NAME)||
DECODE(fkcol3.COLUMN_NAME, NULL, NULL,chr(10)||' ,'||fkcol3.COLUMN_NAME)||
')'||chr(10)||
' REFERENCES &1'||chr(10)||
' ('||pkcol1.COLUMN_NAME||
DECODE(fkcol2.COLUMN_NAME, NULL, NULL,chr(10)||' ,'||pkcol2.COLUMN_NAME)||
DECODE(fkcol3.COLUMN_NAME, NULL, NULL,chr(10)||' ,'||pkcol3.COLUMN_NAME)||
'));'
FROM user_constraints fk --fk to remember
, user_cons_columns fkcol1 --col1 of fk to remember
, user_cons_columns fkcol2 --col2 of fk to remember
, user_cons_columns fkcol3 --col3 of fk to remember
, user_constraints pk --pk constraint of table to set free
, user_cons_columns pkcol1 --col1 of pk to remember
, user_cons_columns pkcol2 --col2 of pk to remember
, user_cons_columns pkcol3 --col3 of pk to remember
WHERE fk.constraint_type = 'R'
AND fk.r_owner = pk.owner
AND fk.r_constraint_name = pk.constraint_name
AND pk.table_name like UPPER('&1')
AND fk.owner = fkcol1.owner
AND fk.constraint_name = fkcol1.constraint_name
AND 1 = fkcol1.position
AND fk.owner = fkcol2.owner(+)
AND fk.constraint_name = fkcol2.constraint_name(+)
AND 2 = fkcol2.position(+)
AND fk.owner = fkcol3.owner(+)
AND fk.constraint_name = fkcol3.constraint_name(+)
AND 3 = fkcol3.position(+)
AND pk.owner = pkcol1.owner
AND pk.constraint_name = pkcol1.constraint_name
AND 1 = pkcol1.position
AND pk.owner = pkcol2.owner(+)
AND pk.constraint_name = pkcol2.constraint_name(+)
AND 2 = pkcol2.position(+)
AND pk.owner = pkcol3.owner(+)
AND pk.constraint_name = pkcol3.constraint_name(+)
AND 3 = pkcol3.position(+);

REM
REM
spool off
set heading on feedback on verify on
prompt * Note: This script to recreate Foreign Keys pointing
prompt * to '&1' is called 'create_fks_to_&1..sql'
prompt
prompt * Also note: This script can only handle up to three
prompt * columns in a foreign key.

[stmontgo]

tks steve, funny, i just finished working on a project with biju (the author of the script)

steve

[pando]

use a ER tool like erwin, designer or embarcadero then reverse engineer the schema and the DDL, easier and safer :0

[stmontgo]

tks all but i still don't have the solution. the first post does not give recursive relationships. For example if A is a parent to B and B is a parent to C if I provide table A to the above script it only lists the FK relationship for B and does not include C.

Also pando yeah I agree that would be nice to use those tools but I would rather execute a script that dynamically drops and recreates all relevant (or I would settle for all) foreign keys in the db. The use of a tool would require user interaction and I would like to automate the procedure

[stmontgo]

never mind, hacked one from asktom, tks for your replies nonetheless

steve