User's Guide
PART 2. Relational Database Concepts
CHAPTER 14. Ensuring Data Integrity
For data to have integrity means that the data is valid—correct and accurate—and that the relational structure of the database is intact. The relational structure of the database is enforced through referential integrity constraints. These are rules that maintain the consistency of data between tables.
Adaptive Server Anywhere supports stored procedures and JDBC, which allow you detailed control over how data gets entered into the database. You can also create triggers: custom procedures stored in the database that are invoked automatically when a certain action, such as an update of a particular column, is carried out.
Procedures and triggers are discussed in Using Procedures, Triggers, and Batches.
How data can become invalidHere are a few examples of how the data in a database may become invalid if proper checks are not made. Each of these examples can be prevented by facilities described in this chapter.
A sales transaction takes place, but the operator entering the date of the transaction does so incorrectly
A zero is missed off a salary entry, making an employee's salary ten times too small
A new department has been created, with dept_id 200, and needs to be added to the department table of the organization's database—but two people enter this information into the table
In a reorganization, the department identified by dept_id 300 is closed down.
Each employee record for employees in this department is given a new dept_id entry, and then the department 300 row is deleted from the department table. But one employee was missed, and still has dept_id 300 in their record.
Integrity constraints belong in the databaseIn order to 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 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. In 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.
How database contents get changedInformation in database tables is changed by submitting SQL statements from client applications. Only a few SQL statements actually modify the information in a database.
Information in a row of a table may be updated, using the UPDATE statement.
An existing row of a table may be deleted, using the DELETE statement.
A new row may be inserted into a table, using the INSERT statement.
Data integrity toolsTo assist in maintaining data integrity, you can use defaults, data constraints, and constraints that maintain the referential structure of the database.
You can assign default values to columns, to make certain kinds of data entry more reliable. For example:
A column can have a current date default for recording the date of transactions with any user or client application action.
A particular kind of default allows column values to be incremented automatically whenever a new row is entered. Items such as purchase orders, for example, can be guaranteed unique sequential numbers in this way without any user action.
These and other column defaults are discussed in Using column defaults.
You can use several types of constraints on the data in individual columns or tables. For example:
A NOT NULL constraint prevents a column from containing a null entry.
Columns can have CHECK conditions assigned to them, to ensure that a particular condition is met by every item in the column. You could ensure, for example, that salary column entries are within a specified range, protecting against user error when typing in new values.
CHECK conditions can be made on the relative values in different columns, to ensure, for example, that in a library database a date_returned entry is later than a date_borrowed entry.
More sophisticated CHECK conditions can be enforced using a trigger. Triggers are discussed in Using Procedures, Triggers, and Batches.
These and other table and column constraints are discussed in Using table and column constraints. Column constraints can be inherited from user-defined data types.
The information in relational database tables is tied together by the relations between tables. These relations are defined by the primary keys and foreign keys built in to the database design. The following integrity rules maintain the structure of the database:
Entity integrity Keeps track of the primary keys. It guarantees that every row of a given table can be uniquely identified by a primary key that guarantees IS NOT NULL.
Referential integrity Keeps track of the foreign keys that define the relationships between tables. It guarantees that all foreign key values either match a value in the corresponding primary key or contain the NULL value if they are defined to allow NULL.
For more information about enforcing referential integrity, see Enforcing entity and referential integrity. For more information about designing appropriate primary and foreign key relations, see Designing Your Database.
You can also use triggers to maintain data integrity. A trigger is a procedure stored in the database that is executed automatically whenever the information in a specified table is altered. Triggers are a powerful mechanism for database administrators and developers to ensure that data is kept reliable.
For a full description of triggers, see Using Procedures, Triggers, and Batches.
SQL statements for implementing integrity constraintsThe following SQL statements are used to implement integrity constraints:
CREATE TABLE statement This statement is used to implement integrity constraints as the database is being created.
ALTER TABLE statement This statement is used to add integrity constraints to an existing database, or to modify constraints for an existing database.
CREATE TRIGGER statement This statement is used to create triggers to enforce more complex business rules.
For full descriptions of the syntax of these statements, see SQL Statements.