Examples: UPDATE rules

For example, you cannot update a department number from the department table if it is still responsible for some project, which is described by a dependent row in the project table.

The following UPDATE fails because the PROJECT table has rows that are dependent on DEPARTMENT.DEPTNO having a value of 'D01' (the row targeted by the WHERE statement). If this UPDATE were allowed, the referential constraint between the PROJECT and DEPARTMENT tables will be broken.

     UPDATE CORPDATA.DEPARTMENT
             SET DEPTNO = 'D99'
             WHERE DEPTNAME = 'DEVELOPMENT CENTER'

The following statement fails because it violates the referential constraint that exists between the primary key DEPTNO in DEPARTMENT and the foreign key DEPTNO in PROJECT:

     UPDATE CORPDATA.PROJECT
             SET DEPTNO = 'D00'
             WHERE DEPTNO = 'D01';

The statement attempts to change all department numbers of D01 to department number D00. Since D00 is not a value of the primary key DEPTNO in DEPARTMENT, the statement fails.