SQL and Relational Theory, 2nd Edition

BUT DOESN’T SOME CHECKING HAVE TO BE DEFERRED?

The arguments of the previous section notwithstanding, the conventional wisdom is that multirelvar constraint checking, at least, does have to be deferred to commit time. By way of example, suppose the suppliers-and-parts database is subject to the following constraint:

     CONSTRAINT CX8
          COUNT ( ( S WHERE SNO = 'S1' ) { CITY }
                    UNION
                  ( P WHERE PNO = 'P1' ) { CITY } ) < 2 ;

This constraint says that supplier S1 and part P1 must never be in different cities. To elaborate: If relvars S and P contain tuples for supplier S1 and part P1, respectively, then those tuples must contain the same CITY value (if they didn’t, the COUNT invocation would return the value two); however, it’s legal for relvar S to contain no tuple for S1, or relvar P to contain no tuple for P1, or both (in which case the COUNT invocation will return either one or zero). Given this constraint and our usual sample values, then, each of the following SQL UPDATEs will fail under immediate checking:

     UPDATE S SET CITY = 'Paris' WHERE SNO = 'S1' ;
     UPDATE P SET CITY = 'Paris' WHERE PNO = 'P1' ;

I show these UPDATEs in SQL rather than Tutorial D precisely because checking is immediate in Tutorial D and the conventional solution to the problem therefore doesn’t work in Tutorial D. What is that conventional solution? Answer: We defer the checking of the constraint to commit time,^[121] and we make sure the two UPDATEs are part of the same transaction, as in this SQL code:

 START TRANSACTION ...