Hi Dave,

This isn't possible in DB2.  I know it's possible in a few other
RDBMS's but not in DB2. There is no "update cascade". As long as there
are no "child" rows, you can do an update of the pk..from the moment
there are child rows, DB2 prohibits this, for your own protection.

with best regards,

Kurt Struyf
Senior database consultant

Competence Partners, Belgium

I beg to differ with Kurt Struyf: there *is* a way to handle updates of
primary keys that have foreign keys. It's just not a particularly DIRECT
way.

Rather than simply updating the primary key directly, which is not possible
due to the Referential Integrity rules and the absence of cascading updates
of primary keys in DB2, you have to go about things in a more roundabout
way:
1. Create a new row containing the desired primary key value in the parent
table. It will have no dependent rows at this point.
2. Find all the rows that are dependents of the old primary key in all of
the dependent tables. Change the foreign keys of those rows to the newly
created value from Step 1. This can be a lot of work if there are many
dependencies; remember, you have to change the dependents of the dependents
and the dependents of the dependents of the dependents, and so on until all
of them are done.
3. Delete the old primary key row.

A simple example to illustrate. I'll adapt from the sample tables that come
with DB2.

Let's say you have two tables, EMP and DEPT. DEPT contains one row for every
department in a company and looks like this:

DEPTNO     DEPTNAME
A00            ADMIN
B01            SALES
C01            EDUC
D01            MANUF

The primary key of DEPT is DEPTNO.

EMP contains one row for each employee in the company and contains columns
EMPNO, LASTNAME, and WORKDEPT. It looks this way:

EMPNO    LASTNAME    WORKDEPT
001        HAAS            A00
002        SMITH            D01
003        BROWN        C01
004        GREEN           C01
005        WHITE        B01

The primary key of this table is EMPNO and WORKDEPT is a foreign key
referring back to the DEPTNO column of the DEPT table.

You want to change the department number for the SALES department from B01
to S01. You try to do this:

update dept
set deptno = 'S01'
where deptno = 'B01'

This fails due to referential integrity; you can't change a primary key that
has dependent rows and department B01 has a dependent row in the EMP table,
specifically WHITE, employee number 005.

Here is how you accomplish what you want to do INDIRECTLY.

First, create the new primary key row. You want the SALES department to have
the primary key S01 so create that row:

INSERT INTO DEPT VALUES('S01', 'SALES');

Second, change all foreign keys that are dependent on the old value, B01, to
the new value, S01. In this case, that is one row in the EMP table:

UPDATE EMP
SET WORKDEPT = 'S01'
WHERE WORKDEPT = 'B01'

Third, delete the old primary key row:

DELETE FROM DEPT
WHERE DEPTNO = 'B01'

That's it: your primary key has the new desired value, all of your dependent
rows now have the new value in their foreign keys, and all references to the
old value are gone.

For what it's worth, the process of generating all the SQL to do this work -
which is usually a lot more work in real life due to much more complex table
relationships - could almost certainly be automated if you wanted to take
the time to do it. All of the relationships and information necessary to do
so are stored in the DB2 catalog and a tool *could* be written that figures
out all of the statements that would be necessary given a particular change
to a specific primary key. If you didn't mind doing that work, it might be a
worthwhile investment if you regularly have to change primary key values for
some reason.

Rhino

Use triggers instead (You can still use RI in a NOT ENFORCED mode to
keep the performance characteristics).
The need for UPDATE CASCADE revolves around the need to update primary
keys, which revoles in turn around the question whether one should use
"natural" keys or not.
Gets very religeous very quickly :-)

Cheers
Serge