Tools for maintaining data integrity

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.

Primary keys guarantee that every row of a given table can be uniquely identified in the table.

The following constrains maintain the structure of data in the database, and define the relationship between tables in a relational database:

Referential constraints Data integrity is also maintained using referential constraints, also called RI constraints (for referential integrity constraints). RI constraints are data rules that are set on columns and tables to control what the data can be. RI constraints define the relationship between tables in a relational database.
NOT NULL constraint A NOT NULL constraint prevents a column from containing a NULL entry.
CHECK constraint 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 are protected from user error when new values are entered.
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.

Column constraints can be inherited from domains.

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. You can also use triggers to maintain data integrity. Triggers can enforce more sophisticated CHECK conditions.

Tools for maintaining data integrity

Defaults

Primary keys

Table and column constraints

Triggers for advanced integrity rules

See also