Examples: UPDATE rules

 

These examples illustrate the UPDATE rules for tables with referential constraints.

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

The following UPDATE statement fails because the PROJECT table has rows that are dependent on DEPARTMENT.DEPTNO that has a value of 'D01' (the row targeted by the WHERE statement). If this UPDATE statement were to be allowed, the referential constraint between the PROJECT and DEPARTMENT tables would 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. Because D00 is not a value of the primary key DEPTNO in DEPARTMENT, the statement fails.

 

Parent topic:

Updating tables with referential constraints