"Dave D" <da*******@yahoo.com> wrote in message
news:qo******************@fe10.lga...
Hi -
I would like to know how the following task is accomplished:
Updating the Primary Key in the parent table when there are other tables
that reference this Key(Foreign Key)
Is there a way to have DB2 handle this task for you..
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
