Browse DevX
Sign up for e-mail newsletters from DevX


Enforce Business Rules for All Your Data with Constraints

The only way to guarantee that your business rules are always enforced for all your data is by using constraints. Learn how to use them to enforce business rules in the database.

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:

 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."

Thanks for your registration, follow us on our social networks to keep up-to-date