Re-defining (Dropping and Adding) Foreign Keys

You may also like...

2 Responses

  1. Paul Turpin says:

    Today I needed to change a delete rule and after reading the ALTER table syntax diagramming and fighting with the syntax for awhile, I decided to do a Google search. I found this post where you stated that you cannot just alter a delete rule. This convinced me to just drop the existing foreign key constraint and redefine it the way I needed it to be.

  2. krishna says:

    Thanks for the detailed post .I feel we can also do the same as below

    To get the details of the foreign key and tables involved before altering the table.

    db2 “select substr(R.reftabschema,1,20) as P_Schema, substr(R.reftabname,1,20) as PARENT,
    substr(R.tabschema,1,20) as C_Schema, substr (R.tabname,1,20) as CHILD,
    substr(R.constname,1,20) as CONSTNAME,
    substr(LISTAGG(C.colname,’, ‘) WITHIN GROUP (ORDER BY C.colname),1,20) as FKCOLS
    from syscat.references R, syscat.keycoluse C
    where R.constname = C.constname and R.tabschema = C.tabschema and R.tabname = C.tabname
    group by R.reftabschema, R.reftabname, R.tabschema, R.tabname, R.constname” >> /dwdmdev/krishna/prim_fore.relation.txt

    Altering the table to disable and enable the foreign key relation
    cat /dwdmdev/krishna/prim_fore.relation.txt | while read P_SCHEMA PARENT C_SCHEMA CHILD CONSTNAME FKCOLS
    do
    echo “ALTER TABLE $C_SCHEMA.$CHILD ALTER FOREIGN KEY $FKCOLS NOT ENFORCED;” >> 2.notenforced.sql
    echo “Alter TABLE $C_SCHEMA.$CHILD ALTER FOREIGN KEY $FKCOLS ENFORCED;” >> enforced.sql

    run db2 -tvf 2.notenforced.sql and proceed with deletes and on completion of deletes run db2 -tvf enforced.sql to roll back the changes,
    done

Leave a Reply

Your email address will not be published. Required fields are marked *