f the several ways to enforce business rules in the database, only constraints can guarantee that your business rules are always enforced for all your data. This article teaches you how to apply foreign key constraints with the ON UPDATE CASCADE
clause to implement waterproof solutions for several common real world problems.
Suppose, for example, you are implementing a bug-tracking system in which you will enforce a business rule: Active tickets cannot be assigned to programmers who have already quit. Consider the following two tables, which will store the data for your system:
CREATE TABLE Programmers(FullName VARCHAR(30) PRIMARY KEY,
Status CHAR(10) NOT NULL CHECK(Status IN('Active', 'Quit'))
CREATE TABLE Tickets(TicketName VARCHAR(30),
Status CHAR(10) CHECK(Status IN('Active', 'Closed')),
ProgrammerName VARCHAR(30) NOT NULL,
ProgrammerStatus CHAR(10) NOT NULL,
CONSTRAINT ActiveTickets_CK CHECK((ProgrammerStatus = Status) OR Status = 'Closed')
Clearly, you can enforce this business rule using either stored procedures or triggers. Obviously, you can bypass any stored procedure if you have sufficient privileges. Less obvious but just as true is that in some cases triggers do not fire, so you can bypass them too. Soon you will learn an alternative implementation that uses only foreign key and check constraints and as such cannot be bypassed.
|Author's Note: Although technically you can wrap a UDF call in a check constraint, such a constraint might not work as you would expect. This article does not use such check constraints. For more details, refer to my previous DevX article, "Avoid These Common Business Rule Implementation Mistakes."|