Sybase 12.4.2 manual Integrity constraints belong in the database, Data integrity overview, 274

Data integrity overview

Integrity constraints belong in the database

Build integrity constraints into database whenever possible

Constraints aid IQ optimization

To help ensure that the data in a database are valid, you need to formulate checks that define valid and invalid data and design rules to which data must adhere. The rules to which data must conform are often called business rules. The collective name for checks and rules is constraints.

Constraints built into the database itself are inherently more reliable than those built into client applications, or spelled out as instructions to database users. Constraints built into the database are part of the definition of the database itself and can be enforced consistently across all applications.

Setting a constraint once, in the database, imposes it for all subsequent interactions with the database, no matter from what source. By contrast, constraints built into client applications are vulnerable every time the software is altered, and may need to be imposed in several applications, or several places in a single client application.

Adaptive Server IQ enforces some constraints but not others. Because IQ data typically is entered by only a few users, and often loaded directly from other databases, IQ databases tend to be less vulnerable than OLTP databases to the kinds of errors that can cause invalid data.

You should declare any constraints that apply, whether Adaptive Server IQ enforces them or not. By declaring constraints, you ensure that you understand your data requirements, and are designing a database that matches the business rules of your organization.

Adaptive Server IQ performs several types of optimization based on the constraints you specify. This optimization does not depend on enforcement of constraints. For the best performance of queries and load operations, put all constraints in the database.

Here is a list of some of the types of optimization that rely on the constraints and other features you build into the database:

•Join indexes optimize queries that join data from different columns. In many cases, the join relationship for a join index relies on the foreign key constraints you specify for the tables being joined.

•Query optimization relies heavily on the CHECK conditions in the table definition.

•PRIMARY KEY and UNIQUE column constraints and the IQ UNIQUE parameter can improve performance for your loads and queries and facilitate automatic index creation.

274