User's Guide

Part III. Using SQL Anywhere

Chapter 18. 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.

SQL Anywhere supports stored procedures, which allow you detailed control over how data gets entered into the database. SQL Anywhere also allows you to 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 the chapter "Using Procedures, Triggers, and Batches".

How data can become invalid

Here 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 SQL Anywhere facilities described in this chapter.

Examples

• Incorrect information
• 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
• Duplicated data
• 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
• Foreign key relations invalidated
• 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 database

In 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.

Build integrity constraints into database

Constraints built in to the database itself are inherently more reliable than those built in to 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 changed

Information in SQL Anywhere 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 tools in SQL Anywhere

To assist in maintaining data integrity, SQL Anywhere provides defaults, data constraints, and constraints that maintain the referential structure of the database.

Defaults

SQL Anywhere allows you to 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".

Constraints

SQL Anywhere also supports 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 the chapter "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.

Entity and referential integrity

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. SQL Anywhere supports two integrity rules that 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 no nulls.
• 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.

In addition, SQL Anywhere provides triggers. 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. Triggers are discussed in the chapter "Using Procedures, Triggers, and Batches".

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 the chapter "Designing Your Database".

SQL statements for implementing integrity constraints

The 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 the chapter "Watcom-SQL Language Reference".