To help maintain data integrity, you can use data constraints and constraints that specify the referential structure of the database.
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. Sybase IQ enforces this constraint.
Columns can have CHECK conditions assigned to them, to specify that a particular condition should be met by every item in the column. You could specify, for example, that salary column entries should 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.
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 candidate keys and foreign keys 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.
The following restrictions affect candidate keys:
A foreign key cannot be a candidate key if a join index exists.
You cannot specify a foreign key constraint to a candidate key that is also a foreign key.
The following integrity rules define 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. All foreign key values either should match a value in the corresponding primary key or contain the NULL value if they are defined to allow NULL.
For more information about referential integrity, see “Declaring entity and referential integrity”.
