Re-defining (Dropping and Adding) Foreign Keys

Have you ever had to alter foreign keys in a way not supported by mere alteration? Dropping and re-creating all of them can be quite a chore, but a little SQL can make it easier. In this post, I’m sharing my experience and SQL in hopes of helping others who have to do the same thing.

Some DBAs think “ON DELETE CASCADE” is evil incarnate. However, in WebSphere Commerce databases, “ON DELETE CASCADE” is critical to the functioning of both the data pruning process, and to the functioning of the stagingprop process that moves data from staging to production, if you have custom tables dependent on stagingproped tables. I would go so far as to say that for a WebSphere Commerce database, you should never define a foreign key without using “ON DELETE CASCADE”. With a web of RI as sticky and tangled, anything else is likely to get you in trouble. This post doesn’t just apply to WebSphere Commerce databases, though – there are tips here you could use in any scenario where you need to work with or re-define foreign keys.

At this point, if you have any confusion on what “ON DELETE CASCADE” is or the differences in delete rules, go check out http://www.ibm.com/developerworks/data/library/dmmag/DMMag_2011_Issue1/DistributedDBA/index.html.

I looked and didn’t find a way to just alter the delete rule for an existing foreign key. Seems to me that would be useful syntax (anyone from IBM DB2 development reading?). While it isn’t a task I have to perform every day, this is the second time in the last year I’ve had to do it across 4 databases each time.

In the specific case that prompted this post, I’m dealing with a WebSphere Commerce database inherited from another company. Whenever I didn’t get to build a database, and another company supported it for an extended period of time, there can be a number of little inconsistencies that need to be corrected.

How I Found the Problem

The problem manifested when I had to delete rows from the SHIPMODE table that had MARKFORDELETE = 1. I did the delete in my development (staging-role) environment, and got SQL0532. In fact, I worked through at least 6 of these errors. In each case, I went to the dependent table manually, and deleted the data there before again trying the delete on the parent table. That looked something like this:

delete from wscomusr.shipmode where markfordelete=1
SQL0532N A parent row cannot be deleted because the relationship
"XHORARIOCORTE.FK_XHRCOR_SHIPMODE" restricts the deletion.

delete from wscomusr.XHORARIOCORTE where shipmode_id in (select shipmode_id from wscomusr.shipmode where markfordelete=1)
Number of rows affected : 8
DB20000I The SQL command completed successfully.

delete from wscomusr.shipmode where markfordelete=1
SQL0532N A parent row cannot be deleted because the relationship
"XSHPDFC.FK_XSHPDFC_SHPMD" restricts the deletion.

delete from wscomusr.XSHPDFC where shipmode_id in (select shipmode_id from wscomusr.shipmode where markfordelete=1)
Number of rows affected : 3407
DB20000I The SQL command completed successfully.
.
.
.
lather, rinse, repeat
.
.
.
delete from wscomusr.shipmode where markfordelete=1
Number of rows affected : 13
DB20000I The SQL command completed successfully.

I finished with that, and put a task on my back-burner list to analyze and re-define the Foreign Keys with “ON DELETE CASCADE”. In retrospect, I could have used SYSCAT.REFERENCES to identify all foreign keys without delete cascade referencing SHIPMODE instead of iterating through failures.

How The Problem Became Important

The next problem occurred when stagingprop was run the next day. It failed on rows in the SHIPMODE table with SQL0532N. DOH! I should have thought of that, since I knew I was relying on stagingprop to move the changes from the dev and staging databases to the QA and Production databases. This moved the back-burner task up to the top of my list.

What Method to Use?

I considered using db2look. I like how db2look captures a number of details when generating syntax. But if I used db2look, I would have to edit the SQL generated to add “ON DELETE CASCADE”. The other hard part would be getting only the statements I want out of db2look. I cannot tell it to just give me the foreign key SQL for foreign keys that are not defined with “ON DELETE CASCADE”. The best I could hope for was to use a table list and specify the names of the problem tables, and then sort out the statements I wanted. Let’s just say that I haven’t had the best luck in getting the filtering by table name options of db2look to work the way I want them to. Granted, part of that impression probably goes back to DB2 7 or 8. In my mind, that would only be a valid approach for up to about 5 Foreign Keys, so I needed to know how many we’re really talking about.

I used the query below to list out the Foreign Keys I would need to re-create. I also had to look up the various values for DELETERULE in the Info Center: http://www.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.sql.ref.doc/doc/r0001057.html

select 
    substr(constname,1,30) constname
    , substr(tabschema,1,18) tabschema
    , substr(tabname,1,20) tabname
    , deleterule 
from syscat.references 
where deleterule != 'C'
with ur;

CONSTNAME                      TABSCHEMA          TABNAME              DELETERULE
------------------------------ ------------------ -------------------- ----------
ANN_CONST2                     WSCOMUSR           GRANNHIST            A
FK_XAGENDA_USER                WSCOMUSR           XAGENDA              A
FK_XEMP_USER                   WSCOMUSR           XEMPRESA             R
FK_XTER_CATENTRY               WSCOMUSR           XTERCERO             R
XCATENTSHIPMDFK_1              WSCOMUSR           XCATENTSHIPMD        A
XCATENTSHIPMDFK_2              WSCOMUSR           XCATENTSHIPMD        A
.
.
.
  40 record(s) selected.

40 is a bit too much to just copy and paste from db2look for me.

With this information, I decided to use SQL to write the statements based on information in the system catalog tables.

Using SQL to Write the Statements

I’ll admit that SYSCAT.REFERENCES has never been one of my strong points in the DB2 catalog tables, so it was a bit of a journey for me to use it to actually write the statements. I was at first attracted to the FK_COLNAMES and PK_COLNAMES columns in SYSCAT.REFERENCES, but those columns do not have the friendliest format for reliably altering into the comma separated list for my alter table statements, so I went instead with joining the KEYCOLUSE table on CONSTNAME (CONSTNAME/REFKEYNAME) and TABNAME (TABNAME/REFTABNAME) and TABSCHEMA (TABSCHEMA/REFTABSCHEMA).

Over at Serge Rielau’s blog I found some nice syntax for actually making a comma separated list that didn’t end with a comma: https://www.ibm.com/developerworks/mydeveloperworks/blogs/SQLTips4DB2LUW/entry/aggregating_strings42?lang=en.
I just love Serge’s stuff when I’m writing more complicated SQL or SQL to really format text strings the way I want them. I’ve found a number of good things from him, especially when dealing with recursion.

I also decided that even though my developers promised not to touch the database for the duration of the change, that I wanted to run each add statement immediately after each drop statement, just in case they weren’t doing exactly as promised – to minimize the time I was exposed by having no Foreign Key in place. So with that in mind, here’s the SQL I came up with:

select  'alter table wscomusr.'
        || TABNAME
        || ' drop constraint '
        || CONSTNAME
        || ';'
        || CHR(10)
        || 'alter table wscomusr.'
        || TABNAME
        || ' add constraint '
        || CONSTNAME
        || ' foreign key ('
        || (select
                SUBSTR(XMLCAST(XMLGROUP(',' || colname AS a) AS VARCHAR(60)), 2)
                from syscat.keycoluse k
                where k.constname=r.constname
            and k.tabschema=r.tabschema
            and k.tabname=r.tabname)
        || ') references wscomusr.'
        || REFTABNAME
        || ' ('
        || (select
                SUBSTR(XMLCAST(XMLGROUP(',' || colname AS a) AS VARCHAR(60)), 2)
                from syscat.keycoluse k
                where k.constname=r.refkeyname
            and k.tabschema=r.reftabschema
            and k.tabname=r.reftabname)
        || ') on delete cascade;'
from syscat.references r
where DELETERULE != 'C'
order by TABNAME
with ur;

That SQL writes the SQL to do the actual work. When piped to a file, it generates a file with statements that look like this:

alter table wscomusr.XAGENDA drop constraint FK_XAGENDA_USER;
alter table wscomusr.XAGENDA add constraint FK_XAGENDA_USER foreign key (USERS_ID) references wscomusr.USERS (USERS_ID) on delete cascade;                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
alter table wscomusr.XAGENDARELPRF drop constraint SQL061016084338510;
alter table wscomusr.XAGENDARELPRF add constraint SQL061016084338510 foreign key (PREFERENCIA_ID) references wscomusr.XPREFERENCIA (PREFERENCIA_ID) on delete cascade;           

The SQL provided should be able to handle Foreign Keys with any number of columns, though I’ve only tested it with one and two column Foreign Keys. Please remember to review the SQL and the output carefully – I make no guarantees about its functionality or performance.

If there were any other changes you wanted to make at the same time (like standardizing Foreign Key names), then you could either alter the SQL to make the changes on writing the statements or alter the file after it is created.

Backout

Since I always have a backout plan or two available, I should share that here too. My Backout plans:
Layer 1: Saved the syntax generated and the delete rules so I could re-write the statements in the script manually to drop and create the primary keys with their original delete rules. Though since I don’t see this as a high risk plan, I did not actually write said backout script
Layer 2: Took a db2look (with -e -a options) of the entire database before running the generated scripts. Theoretically, I could pull out the statements for just he foreign keys I changed.
Layer 3: Restore from backout – since developers were told not to make any changes during my change, I could restore the backup and roll forward through logs to the beginning of my change. Granted, that would take at least 40 minutes, so would not be ideal.

Do I Need to SET INTEGRITY?

There is no need to run “SET INTEGRITY” as a part of this process, as integrity checking is done at the time of the ADD CONSTRAINT alter table statements. If you have multiple constraints on large tables, you may choose to set integrity off before each table and then set it on once all constraints have been added to avoid excessive duration for the integrity setting phases. My tables were very small, and the entire process for all 40 foreign keys took about 5 minutes. Larger tables will absolutely take longer and this whole process could take quite a while.