This article may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist. To maintain the flow of the article, we've left these URLs in the text, but disabled the links.

Pulling Out the Stops

Tom Moreau

One of a DBA's many responsibilities is to ensure that both referential and data integrity are maintained. This month, Tom Moreau shows you how to break the rules–and get away with it–when the need arises.

When a new version of SQL Server (or any other product) is released, I try to make time to reflect on applications I've delivered under the old version to see if there's anything in the new release that would have made me design something differently. Just such a situation occurred when I received version 7.0 of SQL Server.

	I was the DBA on a project at a major Canadian bank. We were managing the distribution of banking cards, and we had to manage cards and customers. A customer could have many cards, but a card belonged to only one customer–once it had been assigned. Until the card was assigned, however, the Customer ID had to be NULL. Now, consider this: The bank can have millions of customers and millions of cards. Cards spend most of their lives assigned to a customer. It would be a real pity to have NULLs just for that small percentage of unassigned cards. (See my article, "Much Ado About Nothing," in the July 1999 SQL Server Professional.)

	One solution is to make the Customer ID column NOT NULL in the Card table and then insert a dummy row in the Customer table. I like to avoid having dummy data, however, since every query you write against such a table has to account for the fact that you have dummy data.

	Wouldn't it be nice if you could insert the row into the Card table with, say, 0 as the Customer ID? You could then assign it to a real Customer later. This would also permit you to do a query for those cards with a Customer ID of 0–the unassigned cards–to determine the number of cards that have yet to be granted to customers. Well, SQL7 allows you to do exactly that. Now you can temporarily turn off constraints and triggers without actually dropping them.

Constraints

Constraints are the means by which you can prevent bad data from entering your database. Constraints aren't pieces of code, but are more like properties you can assign to tables or individual columns to ensure only the right data make it to your table. Constraints are generally a little faster than triggers. The main types of constraints are:

  • Primary key
  • Foreign key
  • Check

	Primary key constraints enforce uniqueness–entity integrity–for a table. There's only one such constraint per table, and it doesn't allow NULLs. You can, however, have a Unique constraint, which does allow NULLs, provided there is only one such NULL. You can have as many of these per table as you like.

	Foreign key (FK) constraints enforce referential integrity (RI)–they ensure that a value that appears in the FK column or columns also appears in the primary key column of the table to which it refers. FK constraints can refer to other tables, but only within the same database.

	Finally, check constraints enforce domain integrity– they limit what values a column can allow.

	All constraints can be "installed" at the time the table is created, although this isn't mandatory. Constraints can be added to or removed from a table as necessary. However, pre-7.0 releases didn't allow you to override or disable constraints. This feature alone allows you to get past the normal constraints for specific situations. Without this capability, you'd have to drop the constraint and then re-apply it. In the meantime, the table would be exposed. In any case, when you went to re-apply the constraint, the action would fail due to the "bad" data.

	Let's return to my bank card example. You receive a shipment of new cards and need to add them to your system. You wish to turn off referential integrity on the Customer ID column only during the load, but you want it enforced at all other times. In the meantime, however, you don't want anyone to access the table. How do you do this? The table creation script looks like this:

  CREATE TABLE Customer
(
  CustomerID   int           PRIMARY KEY
, Name         varchar (20)  NOT NULL
)
CREATE TABLE Card
(
  CardID       int    PRIMARY KEY
, CustomerID   int    NOT NULL
                      CONSTRAINT FK1_Card
                      REFERENCES Customer (CustomerID)
)

	Since you don't want other users to violate constraints, you begin a transaction. Then you disable the constraint on your Card table with an ALTER TABLE statement. Next, you load the Card table with your new rows, but the Customer ID is 0, which would normally violate referential integrity. Finally, you commit the transaction. Take a look at the code:

  -- Relax the constraint
BEGIN TRAN
ALTER TABLE Card NOCHECK CONSTRAINT FK1_Card
-- Load the table
INSERT Card
(CardID, CustomerID)
SELECT
  CardID, 0
FROM NewCard
-- Restore the constraint
ALTER TABLE Card CHECK CONSTRAINT FK1_Card
COMMIT TRAN

	A very cool feature of this deliberate rule-breaking method is that you can subsequently update those rows for everything but the Customer ID without having the foreign key constraint fired. However, when you do attempt to update that FK column, it had better find the key in the Customer table or there will be a foreign key violation.

	This approach will work for overriding FK and check constraints, but not PK or unique constraints, since they are enforced through indexing.

	Be aware that a Schema-Modify lock is acquired on the table that will keep users from accessing even the metadata for the table, so make your transaction short. Although you don't have to put the database into "dbo use only" or "single user" mode, bear in mind that users are out of the table for the duration. Such a batch job would typically be run during periods of very low traffic.

Triggers

Triggers are pieces of code that are fired once any constraints have been validated. They are usually in place to enforce weird business rules or cross-database RI. They have access to the inserted and deleted virtual tables. These tables are essentially views of the transaction log, and they allow you access to the rows that caused the trigger to fire.

	There are times when you just don't want the trigger to fire and other times when you do. Once again, SQL7 comes to the rescue. You can disable a trigger without actually dropping it for those oddball cases. For example, you could have a daily batch process running where you don't want the trigger to fire until during online processing, when you do want it to fire. Here's how:

  ALTER TABLE Card DISABLE TRIGGER tr_Card
… do your stuff …
ALTER TABLE Card ENABLE TRIGGER tr_Card

	If you have a constraint on a table, the constraint fires before the trigger. This means that if the constraint validation fails, the trigger won't fire. For example, if you wish to delete an order and its details, SQL Server will stop the delete trigger from firing if there are any rows in the order details table, even though the trigger was in place to delete such rows! It would be nice to get around this little bugbear, too.

	Using the preceding Card and Customer scenario, let's say that you want to delete a customer and all of her cards. The solution is to place the FK constraint on the dependent table–Card–as per usual, and also install the delete trigger on the independent table–Customer. Now, when you need to "violate" RI, just disable the constraint on the Card table, just as you did before. At this point, the delete trigger on Card will fire. Bear in mind that, to plug the consistency holes, you should be doing this inside a transaction–which brings up the problem of the Schema-Modify locks again.

	You can get past this by adding the functionality that the constraint would normally handle inside a trigger on the dependent table. In other words, add an insert/update trigger to the Card table to fill in for the FK constraint. Now, all you have to do is relax the FK constraint without the transaction and the "pinch-hit" RI trigger is already in the starting blocks.

	SQL7 now allows you to have several different triggers of the same type (for instance, three insert triggers). You can leverage this feature by turning triggers on and off as required.

Conclusion

SQL7's ALTER TABLE statement permits you to break the rules of referential and domain integrity for those odd cases where you want to make an exception. Used carefully, this feature allows you to circumvent traditional, hard-and-fast constraints to solve real-world problems.

Download STOPS.SQL

Tom Moreau, B.Sc., Ph.D., is a Microsoft Certified Systems Engineer and Microsoft Certified Database Administrator. He's an independent consultant who specializes in Sybase and Microsoft SQL Server database administration in the Toronto area. Tom also speaks at SQL Connections (www.sqlconnections.com, formerly SQL DevCon). tom@cips.ca.

To find out more about Microsoft SQL Server Professional and Pinnacle Publishing, visit their website at http://www.pinpub.com/html/main.isx?sub=57

Note: This is not a Microsoft Corporation website. Microsoft is not responsible for its content.

This article is reproduced from the March 2000 issue of Microsoft SQL Server Professional. Copyright 2000, by Pinnacle Publishing, Inc., unless otherwise noted. All rights are reserved. Microsoft SQL Server Professional is an independently produced publication of Pinnacle Publishing, Inc. No part of this article may be used or reproduced in any fashion (except in brief quotations used in critical articles and reviews) without prior consent of Pinnacle Publishing, Inc. To contact Pinnacle Publishing, Inc., please call 1-800-493-4867 x4209.