Data Integrity Tools

Constraints

There are constraint types you can use on the data in individual columns or tables:

• A NOT NULL constraint prevents a column from containing a null entry. SAP Sybase IQ enforces this constraint.
• Columns can have CHECK conditions assigned to them, to specify that a particular condition should be met by every row for that column, for example, that salary column entries must be within a specified range.
• CHECK conditions can be made on the relative values in different columns, to specify, for example, in a library database that a date_returned entry is later than a date_borrowed entry.

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 candidate keys and foreign keys that are built into the database design.

A foreign key is made up of a column or a combination of columns. Each foreign key relates the information in one table (the foreign table) to information in another (referenced or primary) table. A particular column, or combination of columns, in a foreign table is designated as a foreign key to the primary table.

The primary key or column (or set of columns) with a unique constraint is known as a candidate key. The referenced column or set of columns must be a candidate key and is called the referenced key. You cannot specify a foreign key constraint to a candidate key that is also a foreign key.

These referential integrity rules define the structure of the database:

• Keep track of the primary keys, guaranteeing that every row of a given table can be uniquely identified by a primary key that guarantees no nulls.
• Keep track of the foreign keys that define the relationships between tables. All foreign key values must either match a value in the corresponding primary key, if they are defined to allow NULL, or contain the NULL value.