Chapter 8 Data Integrity and Consistency

Integrity and Consistency Features

Sybase pioneered server-enforced data integrity that removes the responsibility of enforcing integrity from individual client applications. Using Transact-SQL extensions, application developers can create organization-wide business rules and integrity controls, which SQL Server stores centrally and applies to client applications network-wide.

Sybase triggers and stored procedures are the primary components responsible for centrally enforcing data integrity and consistency. They form an unbreachable wall around the data, so application designers no longer need to program integrity checks within each application. Other vendor's triggers don't have the power to enforce such constraints¯they can only report errors, requiring each application to include code to preserve data integrity and consistency.

You can also combine ANSI/ISO SQL standard declarative referential integrity features with Sybase procedural (trigger-based) integrity mechanisms. And SQL Server provides security features that relate to data integrity, including account locking, groups, and roles.

This chapter describes a variety of Sybase tools and mechanisms that guarantee the integrity, consistency, and recoverability of your data. It covers the following topics:

Enforcing Data Integrity with Transact-SQL

Data integrity refers to the accuracy and consistency of the data in the database. A domain is the set of values that the table designer specifies to be allowable for a particular column. SQL Server uses domain constraints to guarantee that any value a user enters into the column is a member of the domain.

Transact-SQL supplies all the tools you need to create integrity constraints. They include the create table command and SQL Server datatypes, rules, defaults, triggers, and stored procedures (described in Chapter , ``Transact-SQL and Utilities''). At the most basic level, SQL Server verifies that the value a user enters conforms to the datatype requirements associated with the column. To define more specific domains, you can create rules and check constraints, which limit the values users can insert into a column by checking the value against a condition you specify.

Using defaults facilitates consistency because any column entry for which there is an ambiguous or nonexistent value is automatically assigned the default value (such as "unknown" or "to be determined"). This makes such values easy to identify and handle.

Figure 8-1 shows the relationship between SQL Server database objects in their roles as integrity enforcement mechanisms. The sections below describe these roles and interactions.

Figure 8-1: Relationship between objects as integrity mechanisms

Summary of Integrity Constraints

Table 8-1 summarizes the types of data integrity constraints and the Transact-SQL options available for enforcing them.

Table 8-1: Data integrity constraint options
Integrity concern	Function	Options
Uniqueness	Guarantees that no two rows in a table have the same non-null values. Often used for primary keys.	create table... unique create unique index
Uniqueness and completeness	A more restrictive unique constraint. Null values are not allowed.	create table...primary key create unique index on a column with not null specification
Referential integrity	Insures that a primary key exists before a foreign key can be inserted in a detail table.	create table...references create trigger
Check constraint	Insures that a value in a column meets specified conditions	create table...check create rule create trigger
Defaults	Provides a value for a column if no value is entered by the user	create table...default create default

Unique Constraints

Unique constraints govern the degree to which entries in a column must be unique with respect to null values. Transact-SQL provides two mechanisms for implementing unique constraints using the create table command. Both conform to ISO/ANSI SQL 1989 standards for create table.

The unique keyword allows null values, but all non-null values must be unique. No user can insert a primary key that duplicates another value.
The primary key keyword does not allow null values. All values must be unique.

You can create a unique constraint by specifying unique when you issue a create index command on an existing table. If the definition of the column on which the index is created does not allow null values, the result is effectively a primary key constraint. The Transact-SQL extension alter table lets you drop or modify the unique or primary key constraints at any time.

Referential Integrity Constraints

Referential integrity constraints guarantee that each foreign key in a detail table has a valid primary key in the master table. The following two mechanisms enforce referential constraints:

Referential constraint enforces only primary key-foreign key referencing
Trigger provides a greater range of integrity checks than referential constraints

Figure 8-2 shows the entity relationship between two tables from the pubs2 sample database. The pub_id column in the publishers table is a unique primary key. Referential integrity guarantees that books added to the titles table include a pub_id that already exists in publishers.

Figure 8-2: Referential integrity and primary keys

Rules and Check Constraints

A rule or check constraint limits the values allowed in a column. Every time a value is entered through an insert or update command, SQL Server checks it against the most recent rule or constraint on the column. Data entered prior to the creation and binding of a rule or the addition of a constraint is not checked. You can use the alter table command to drop or change a check constraint.

Check constraints are included in the table definition, as this example illustrates.

create table publishers ( pub_id char(4) 

	check (pub_id in ("1389", "0736", "0877"))

		pub_name char(40),

	etc.)

Rules are created with the create rule command, and then bound to a column or user-defined datatype with the sp_bindrule system procedure. The rule that the next example creates enforces the same domain constraints as the declarative constraint created by the last example.

create rule pub_idrule

	as @pub_id in ("1389", "0736", "0877") 

sp_bindrule pub_idrule, "publishers.pub_id"

You can bind the same rule to many columns. For example, if many tables use an employee_id column, you can create one rule and bind it to all of the employee_id columns in the database. An even simpler approach is to create a user-defined datatype (say, employee_id_type) and bind the rule to the datatype. Whenever you create a table with an employee_id_type column, the rule is bound to that table column. Triggers can enforce more complex types of domain constraints as described later in this chapter.

Defaults

Defaults define a default value that SQL Server inserts if a value is not supplied (an error results if you supply an invalid value). You can create defaults using create table's create default and default options. create default defines a default value and links it to a single column, to a number of columns, or to all the columns of a specified user-defined datatype. For example, you might create the default value "undefined" for SQL Server to insert a user does not specify a value. You can drop or change a default at any time.

User-Defined Messages for Constraints

The Sybase system procedures sp_addmessage and sp_bindmessage provide a way to create custom messages for the application that you can bind to check constraints and referential integrity constraints. The following example creates a constraint called positive_bal that ensures that the account balance is greater than zero.

create table account

(

acct_number  int

branch       int

balance      money

 	constraint positive_bal check (balance > 0))

sp_addmessage 30100, NULL, "You can not perform 

transactions that result in a negative account balance."

sp_bindmsg positive_bal, 30100

Any update operations that violate this constraint yield the following message:

You cannot perform transactions that result in a negative 

account balance.

Referential Integrity and Triggers

A trigger is a special kind of stored procedure that goes into effect when you insert, delete, or update data in a specified table. Triggers enforce referential integrity by maintaining consistency within logically-related data in different tables.

The main advantage of triggers is that they are automatic¯they work no matter what caused the data modification¯a clerk's data entry or an application action.

There are three types of triggers; one for each type of data modification operations: update trigger, insert trigger, or delete trigger. You can also create a single trigger that is fired by any of the three operations.

Special control of flow syntax in the trigger can also manage program logic to perform different actions based on which column was affected by an update:

if update (column_name)

begin

	SQL Statements

end

if update (column_name) and update (column_name)

begin

	SQL Statements

end

The trigger "fires" immediately after a data modification statement completes. SQL Server treats the trigger and the statement which fires it as a single transaction that can be rolled back from within the trigger. If a severe error is detected, the entire transaction can be rolled back. Transact-SQL also provides a rollback trigger command to roll back only data modifications performed in the trigger.

Here are some of the reasons that triggers are so useful:

Triggers can "cascade" changes through related tables in the database. For example, a delete trigger on the title_id column (the primary key) of the titles table could cause a corresponding deletion of matching rows in other tables, using the title_id column as a unique key to locating rows in the titleauthor, salesdetail, and roysched tables.
Triggers can perform the same job as referential integrity constraints, disallowing or rolling back changes that would violate referential integrity. Such a trigger would take effect when you try to insert a foreign key that doesn't match any primary key. For example, you could create an insert trigger on titleauthor.title_id that rolled back an insert if the new value did not match some value in titles.title_id.
Triggers can enforce business rules that are much more complex than those defined with rules or check constraints. Unlike rules and check constraints, triggers can reference columns or database objects. For example, a trigger can roll back updates that attempt to increase a book's price by more than 1 percent of the advance.
Triggers can also perform simple "what-if" analyses: a trigger can compare the state of a table before and after a data modification, and take action(s) based on that comparison.

Multirow Considerations

Each trigger fires only once per query. If trigger actions depend on the number of rows a data modification affects, your trigger can test for multirow data modifications and take appropriate action.The @@rowcount variable, which stores the number of rows affected by the most recent data modification operation, lets you check how many rows the data modification affected, and perform different actions depending on the results. Multirow considerations are particularly important when the trigger automatically recalculates summary values (ongoing tallies).

Conditional Triggers

The triggers described so far consider each data modification statement as a whole. If one row of a four-row insert was unacceptable, the whole insert was unacceptable and the transaction was rolled back.

However, you don't have to roll back all data modifications simply because some of them are unacceptable. A trigger incorporating a correlated subquery can examine the modified rows one by one. The trigger can then take different actions on different rows.

Nested and Recursive Triggers

Triggers can nest¯in other words, a trigger can call other triggers. A trigger that changes a table on which there is another trigger can fire that second trigger. This in turn can fire a third trigger, and so forth.

Triggers can nest to a depth of 16 levels. If any trigger in the chain sets off an infinite loop, the nesting level is exceeded and the trigger aborts. You can use nested triggers to perform useful housekeeping functions such as storing a backup copy of rows affected by a previous trigger.

You can also control whether a trigger can call itself recursively¯that is, whether an update trigger can call itself in response to a second update to the same table within the trigger. For example, if an update trigger on one column of a table results in an update to another column, the update trigger fires repeatedly if you use the set option to enable trigger recursion.

Database Consistency Checking

To check the logical and physical consistency of a database, you can use the Transact-SQL dbcc command. dbcc calls the Database Consistency Checker, which is actually a set of utilities that are used when a severe system error has been generated or used by the System Administrator as a periodic check.

The Database Consistency Checker verifies page allocation and linkage, sort order accuracy, index integrity, and system table integrity with minimal interference with other user tasks. It provides a range of levels and options:

Full database checking: three commands can perform all of the consistency checks required to insure the integrity of a user database.
Checking individual tables and indexes. In systems that have constant high usage, you can control the order and timing of database consistency checks on tables used in critical applications.
Full, fast, and optimized modes for some commands ensure that regular consistency checks protect your data with minimal impact on applications.

You can use the Database Consistency Checker to analyze and correct many kinds of space utilization and allocation errors before they threaten data integrity.

[Top] [Prev] [Next] [Bottom]