Sybase Adaptive Server IQ 12.4.2, Administration And Performance Manual

Page: 294 / 536

Data integrity overview
274
Integrity constraints belong in the database
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.
Build integrity
constraints into
database whenever
possible 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.
Constraints aid IQ
optimization 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.