Database: Fields: Data Integrity

The integrity of a database depends on the correctness of the data in the database. There are different types of errors and several methods to handle them in SQL. The most important method is to specify constraints when defining a field in a table. Most database systems will enforce these constraints with no additional programming. Many systems also all defining triggers which are statements to be performed when a particular condition occurs.

Required Data

Problem: Some fields must not be NULL.
Example:
SQL Solution: In the CREATE TABLE statement specify the NOT NULL constraint for that field.

Unique Values

Problem: Certain values in a column must be unique.
Example: A primary key for something like an employee number must be unique.
SQL Solution: Specify the PRIMARY KEY or UNIQUE constraints for that field.

Valid Data Values

Problem: There may be constraints on certain data values.
Example: A person's age may not be a negative number.
SQL Solution: Specify the CHECK constraint when creating that field in a table. The field which is the result

Consistency

Problem: Updates to a database often involve changes to several tables. No access should be allowed when the table is in an intermediate, inconsistent state.
Example:
SQL Solution: Use transactions.

Other Conditions, Business Rules, etc

Problem: A particular database may have complex conditions that apply to their situation.
Example: [TO DO]
SQL Solution: These need to be enforced by some combination of constraints, triggers, and programs.