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'))) go 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') ) go
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.”|
Using Constraints to Guarantee Your Data’s Integrity
This section shows you how to add a unique constraint on the example bug-tracking system’s Programmers table, and a foreign key and a check constraint on its Tickets table. These three constraints working together will absolutely guarantee the integrity of your data.
First, you need to add one more column, ProgrammerStatus, to your Tickets table:
CREATE TABLE Tickets(TicketName VARCHAR(30), Status CHAR(10) CHECK(Status IN('Active', 'Closed')), ProgrammerName VARCHAR(30) NOT NULL, ProgrammerStatus CHAR(10) NOT NULL)
You can make sure that “Active tickets cannot be assigned to programmers who have already quit” using a simple check constraint, as follows:
ALTER TABLE Tickets ADD CONSTRAINT ActiveTickets_CK CHECK((ProgrammerStatus = Status) OR Status = 'Closed')
The difficult part of the solution is guaranteeing that your Tickets.ProgrammerStatus value always matches the corresponding value in Programmers.Status. In such situations, foreign key constraints with the ON UPDATE CASCADE clause come in very handy. These constraints cannot only guarantee that your Tickets.ProgrammerStatus value always matches the corresponding value in Programmers.Status, but they also cannot be bypassed whatsoever.
To see this portion of the solution in action, begin by including your Programmers.Status column in a UNIQUE constraint, as follows:
ALTER TABLE Programmers ADD CONSTRAINT Programmers_UNQ UNIQUE(FullName, Status)
Now you can have a foreign key constraint refer to both the Programmers.name and Programmers.Status columns, as follows:
ALTER TABLE Tickets ADD CONSTRAINT Tickets_FK FOREIGN KEY(ProgrammerName, ProgrammerStatus) REFERENCES Programmers(FullName, Status) ON UPDATE CASCADE
The ON UPDATE CASCADE clause is essential?it guarantees that whenever you change Programmers.Status, your change automatically propagates into Tickets.ProgrammerStatus.
To verify that the constraints that you have just added actually work, add some test data like this:
INSERT Programmers(FullName, Status) VALUES('Jane Stork', 'Active') INSERT Tickets(TicketName, Status, ProgrammerName, ProgrammerStatus)
VALUES('Cannot open report', 'Active','Jane Stork', 'Active') INSERT Tickets(TicketName, Status, ProgrammerName, ProgrammerStatus)
VALUES('Cannot save changes', 'Closed','Jane Stork', 'Active') go
Now use the following commands to verify that a programmer’s status cannot be set to Quit as long as that programmer has active tickets assigned to him or her:
-- must fail: UPDATE Programmers SET Status = 'Quit' /* Server: Msg 547, Level 16, State 1, Line 1 UPDATE statement conflicted with TABLE CHECK constraint 'ActiveTickets_CK'.
The conflict occurred in database 'Sandbox', table 'Tickets'. The statement has been terminated. */
Close the active ticket first, and you then can set the programmer’s status to Quit all right:
UPDATE Tickets SET Status = 'Closed' UPDATE Programmers SET Status = 'Quit' -- Programmer Status is 'Quit' now SELECT * FROM Tickets
Did You Know You’re Using Denormalization?
With the preceding solution, you have in fact denormalized your Tickets table. As such, the table now uses up more storage. Also note that you have increased lock contention on that table. You can easily see this for yourself with the following procedure:
- Begin a transaction and update the Programmer.Status column.
- Do not commit your transaction (note that it has acquired update lock(s) on your Tickets table).
- Rollback your transaction.
- Drop your foreign key constraint.
- Begin another one.
- Update the Programmer.Status column again (note that this time your transaction has not acquired any update locks on your Tickets table).
You’ll find that denormalization is relatively cheap in this case?you do not have to worry about the integrity of your data because the foreign key constraint does it for you.
The Price Tag and the Alternatives
Alternatively, you can use either triggers or stored procedures to implement this business rule. To decide which solution works best for you, consider the advantages of the constraints solution:
- Constraints provide a waterproof solution. Once they are in place, they guarantee that all your data is clean.
- You implement your business rule only once, in only one place.
- Typically, constraints work faster than queries in your stored procedures and triggers.
To be fair to yourself, consider the disadvantages of this solution as well:
- You have to add an additional column.
- You have to add two additional indexes.
- You increase lock contention on your child table.
Clearly, if your business absolutely does not tolerate mismatches between statuses of tickets and programmers, the waterproof solution described in this article is for you. However, if your business will not suffer too terribly from just a few mismatches, then the price you have to pay for the constraints solution may be too steep.
Applying This Solution to Other Situations
Suppose your client is a doctor’s office whose hours are from 8 AM to 8 PM, and you want to prevent its scheduling system from saving 7:30-to-8:15 AM appointments in the database. You can use the same approach to have your database guarantee that appointments completely fit into the office hours. You also can use the same approach to implement process flow business rules.