Declarative Data Protection

Abstract

In this chapter, you’ll finalize the predicates and domains that have been specified during the design process. When you discuss data integrity, you’re looking at applying a body of rules to a given table, or set of tables, to ensure that stored values are always valid. Maintaining your database in a state where data is always within your original design specifications is at the heart of a great implementation. No matter how good or bad the tables have been designed, the way the user perceives the database will be based on how well the data is safeguarded. When developing a data integrity strategy, you need to consider several different scenarios:

• Users using custom front-end tools

• Users using generic data tools such as Microsoft Access

• Routines that import data from external sources

• Raw queries executed by data administrators to fix problems caused by user error

Each of these poses different issues for your integrity scheme, and what is more important, each of these scenarios (with the possible exception of the second) form part of every database system that you develop. To best handle each scenario, you must safeguard the data, using mechanisms that work independently of the user. This makes sure that the user/programmer does not “forget” about a rule and accidentally input incorrect data. There are four possible ways you can use code to maintain consistent data:

• Datatypes and constraints are simple, straightforward resources that form an integral part of the table definition. They are very fast mechanisms that require little coding, and their use can give you some important performance gains. Such constraints include actions such as defining data ranges in columns (for example, column must be greater than 10, or if column l = 1 then column2 > 1).

• This is the best place to protect the data because most of the work is handled by SQL Server, and the user cannot get around this by any error in an external program.

• Triggers differ from constraints in that they are pieces of code attached to a table (or tables). This code is automatically run whenever the event(s) you have specified occur in the table(s). They are extremely flexible and can access multiple columns, multiple rows, multiple tables, and even multiple databases. As a simple case, let’s consider a situation in which you want to ensure that an update of a value is performed on both the tables where it occurs. You can write a trigger that disallows the update unless it occurs in both tables.

• This is the second best place to protect the data. You do have to code every trigger rule in T-SQL, but the user cannot get around this by any error in an external program.

• Stored procedures are pieces of code, stored in the database, that deal with tables in a very flexible manner, so that different business rules can be applied to the same table under different circumstances. A simple example of a stored procedure is one that returns all the data held in a given table. In a more complex scenario, they can be used to grant different permissions to different users regarding manipulation of tables. This isn’t all that great a solution to protect the data because you have to code the rules into any procedures that access the data. However, because it’s in the central location, you can use this to implement rules that may be different based on various situations.

• Client executable code is useful to deal with situations in which business rules are optional or flexible in nature. A common example is asking the user “Are you sure you wish to delete this row?” SQL Server is a server product, and if you ask it to delete data, it deletes data. Most server products work in this manner, leaving the client programs the responsibility of implementing flexible business rules and warnings. Bear in mind that applications come and go, but the data must always be protected.