Filter
Top Products
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.