Data integrity tools

To maintain data integrity, you can use defaults, data constraints, and constraints that maintain the referential structure of the database.

Defaults

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 value for recording the date of transactions with any user or client application action.
  • Other types of default values allow column values to increment automatically without any specific user action other than entering a new row. With this feature, you can guarantee that items (such as purchase orders for example) are unique, sequential numbers.

For more information about these and other column defaults, see Using column defaults.

Constraints

You can apply several types of constraints to the data in individual columns or tables. For example:

  • A NOT NULL constraint prevents a column from containing a NULL entry.
  • A CHECK constraint assigned to a column can ensure that every item in the column meets a particular condition. For example, you can ensure that Salary column entries fit within a specified range and thus protect against user error when entering in new values.
  • CHECK constraints can be made on the relative values in different columns. For example, you can ensure that a DateReturned entry is later than a DateBorrowed entry in a library database.
  • Triggers can enforce more sophisticated CHECK conditions. See Using procedures, triggers, and batches.

As well, column constraints can be inherited from domains. For more information about these and other table and column constraints, see Using table and column constraints.

Entity and referential integrity

Relationships, defined by the primary keys and foreign keys, tie together the information in relational database tables. You must build these relations directly into 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 and creating your database.

Triggers for advanced integrity rules

You can also use triggers to maintain data integrity. A trigger is a procedure stored in the database and executed automatically whenever the information in a specified table changes. Triggers are a powerful mechanism for database administrators and developers to ensure that data remains reliable.

For more information about triggers, see Using procedures, triggers, and batches.

Copyright © 2008, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.0