RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


Enforce Business Rules for All Your Data with Constraints : Page 2

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.

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:

  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:

  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 

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:

  1. Begin a transaction and update the Programmer.Status column.
  2. Do not commit your transaction (note that it has acquired update lock(s) on your Tickets table).
  3. Rollback your transaction.
  4. Drop your foreign key constraint.
  5. Begin another one.
  6. 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.

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