recreate all foreign keys
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: recreate all foreign keys

  1. #1
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187

    recreate all foreign keys

    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
    I'm stmontgo and I approve of this message

  2. #2
    Join Date
    May 2002
    Posts
    2,645
    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.

  3. #3
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    tks steve, funny, i just finished working on a project with biju (the author of the script)

    steve
    I'm stmontgo and I approve of this message

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    use a ER tool like erwin, designer or embarcadero then reverse engineer the schema and the DDL, easier and safer :0

  5. #5
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    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
    I'm stmontgo and I approve of this message

  6. #6
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    never mind, hacked one from asktom, tks for your replies nonetheless

    steve
    I'm stmontgo and I approve of this message

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width