Defensive Database Programming with SQL Server: The Ticket-Tracking System (part 1) - Enforcing business rules using constraints only

1/17/2011 5:32:15 PM
We implemented a trigger-based solution to the problem of assigning tickets in a ticket-tracking system. Our AFTER UPDATE trigger, dbo.Developers_Upd, enforced the following two business rules:
  • developers cannot go on vacation if they have active tickets assigned to them

  • inactive tickets cannot be changed to active status if they are assigned to developers that are on vacation.

Here, we'll re-implement these business rules using only constraints and, in addition, we'll be able to enforce the rule that "newly added active tickets cannot be assigned to a developer on vacation," which would have required an additional AFTER INSERT trigger.

Listing 1. Recreating the Developers and Tickets tables.

Enforcing business rules using constraints only

The Tickets table has an extra column, DeveloperStatus, which we will use in a CHECK constraint that attempts to enforce both our business rules, as shown in Listing 2.

Listing 2. The CHK_Tickets_ValidStatuses constraint enforces both business rules.

However, this constraint makes a serious assumption, and we do not know yet if we can enforce it. Can we really guarantee that the Tickets.DeveloperStatus column will always match the Developers.DeveloperStatus column, for the assigned developer?

The answer is "maybe." FOREIGN KEY constraints are supposed to guarantee that columns in different tables match and the one shown in Listing 3 attempts to do just that. We'll discuss why we need the ON UPDATE CASCADE clause shortly.

Listing 3. FK_Tickets_Developers_WithStatus attempts to ensure that, for a given developer, the relevant column values match.

Unfortunately, when we run Listing 3, it fails. Fortunately, the error message is very explicit and clear.

Listing 4. A very clear error message.

As the error states, the column, or combination of columns, to which a FOREIGN KEY refers in the parent table, in this case (DeveloperID, DeveloperStatus), must be unique. The uniqueness can be enforced very easily using a UNIQUE constraint or, as shown in Listing 5, a UNIQUE index.

Listing 5. Enforcing the uniqueness of (DeveloperID, DeveloperStatus) in the Developers table.

The reason I chose to use an index here, rather than a constraint, is because I try to use the latter strictly to enforce business rules, and the former for performance or other issues. In this case, we are not enforcing a business rule (as DeveloperID by itself is already a candidate key) so much as overcoming a SQL Server technicality, and therefore I chose to use an index.

Now Listing 3 will complete successfully. Before testing our solution, let's summarize the changes we have made so far.

  • Added a new column, DeveloperStatus, to the Tickets table.

  • Added a CHECK constraint, CHK_Tickets_ValidStatuses, to enforce our business rules.

  • Created a UNIQUE index on (DeveloperID, DeveloperStatus) in the Developers table, which allowed these two columns to be used as the parent columns in a foreign key relationship.

  • Created a dual-key FOREIGN KEY constraint on the Tickets table, FK_Tickets_Developers_WithStatus, which includes an ON UPDATE CASCADE clause.

We need to realize that in this solution one and the same information, the status of a developer, can be stored in more than one place: both in a row in the DevelopersTickets table. Clearly this is de-normalization. Usually de-normalization is common practice in data warehousing environments, but is frowned upon in OLTP systems. However, in this particular case, de-normalization is very useful. table and, if the developer has tickets, in each corresponding row in the

Note that, at this time, we have two FOREIGN KEY constraints referring from Tickets to Developers:

  • FK_Tickets_Developers, which relates a single column, AssignedToDeveloperID, in Tickets to its equivalent in Developers

  • FK_Tickets_Developers_WithStatus, which relates two columns, (AssignedToDeveloperID, DeveloperStatus), in Tickets to their equivalent in Developers.

The reason why we need them both will soon become clear. Let's see how our constraints work. Listing 7-6 adds a single developer to our system, who is on vacation.

Listing 6. Adding a developer who is on vacation.

Listing 7 shows that our CHECK constraint prevents an attempt to assign an active ticket to a developer on vacation.

Listing 7. We cannot add an active ticket assigned to a developer on vacation.

Also, our dual-column FOREIGN KEY prevents us from cheating the system by adding a ticket with a DeveloperStatus that does not match the status of the assigned developer.

Listing 8. The DeveloperStatus must match the Status of the assigned developer.

Listing 9 shows that we can add a closed ticket, but cannot reopen it, while the developer is still on vacation.

Listing 9. Adding a closed ticket and a failed attempt to reopen it.

Thanks to the ON UPDATE CASCADE clause on our FOREIGN KEY constraint, when our developer returns from vacation and is active again, his changed status, as reflected in the Developers table, is automatically propagated to the DeveloperStatus column of the Tickets table, as demonstrated in Listing 10.

Listing 10. Justin's changed status is propagated to the ticket assigned to him.

This automatic propagation of values in the Developer.DeveloperStatus column to the Tickets.DeveloperStatus column, via the cascading update in the FOREIGN KEY constraint, is the bedrock of this technique. Now, if we attempt to reopen the ticket, by rerunning the UPDATE statement from Listing 9, it will succeed because the DeveloperStatus column for the ticket correctly indicates that the developer is active.

Continuing our testing, we need to ensure that our developer cannot go on vacation if any active tickets are assigned to him, as verified by Listing 11.

Listing 11. Justin has an active ticket, so he cannot go on vacation.

If we close the ticket, Justin can begin his vacation.

Listing 12. Closing the ticket allows Justin to take a vacation.

Alternatively, we can assign the ticket to another developer.

Listing 13. Reassigning an active ticket.

Also we need to verify that our system works when we modify Developers.DeveloperID column, as shown in Listing 14.

Listing 14. The modified Developers.DeveloperID value propagates into the Tickets table.

As we have seen, when we modify Developers.DeveloperID, the change propagates into the Tickets table. This was not one of our requirements, but is a side effect of the ON UPDATE CASCADE clause. We'll modify our solution later, and this side effect will be gone.

So far, our constraints have worked as expected in all the cases. Of course, if we were rolling out a production system, our testing would be far from complete. For example, we should test cases when more than one ticket is assigned to a developer, cases where we modify more than one row, and the case where we modify the Developers.DeveloperID column for more than one row. However, we shall not demonstrate complete testing here; the solution presented here passes complete testing, and I encourage you to try out different test cases as an exercise.

  •  SQL Server 2008 : Working with DML Queries - Using the MERGE Statement
  •  Defensive Database Programming with SQL Server : Client-side Error Handling
  •  SQL Server 2008 : Working with DML Queries - Using the DELETE Statement
  •  Programming Microsoft SQL Server 2005: Using Data Mining Extensions (part 2) - Data Mining Predictions Using DMX
  •  Programming Microsoft SQL Server 2005: Using Data Mining Extensions (part 1) - Data Mining Modeling Using DMX
  •  SQL Server 2008 : Working with DML Queries - Using the UPDATE Statement (part 2)
  •  SQL Server 2008 : Working with DML Queries - Using the UPDATE Statement (part 1)
  •  Azure Programming Considerations
  •  Programming with SQL Azure : Record Navigation in WCF Data Services
  •  Defensive Database Programming with SQL Server : TRY...CATCH Gotchas (part 2) - TRY...CATCH blocks cannot catch all errors
    Video tutorials
    - How To Install Windows 8

    - How To Install Windows Server 2012

    - How To Install Windows Server 2012 On VirtualBox

    - How To Disable Windows 8 Metro UI

    - How To Install Windows Store Apps From Windows 8 Classic Desktop

    - How To Disable Windows Update in Windows 8

    - How To Disable Windows 8 Metro UI

    - How To Add Widgets To Windows 8 Lock Screen

    - How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010
    programming4us programming4us