Database integrity constraints are a set of rules, defined by database administrator or application developer to maintain the assurance of accuracy and consistency of data. It ensures that the data insertion, updating, and other processes have to be performed in such a way that data integrity is not violated.

Types of Integrity Constraint

1. Domain Constraints
2. Primary Integrity Constraints
3. Referential Integrity Constraints
4. Key Constraints

Domain Constraints

Domain constraints can be determined as the valid set of values for an attribute. The data type of database includes integer, character, string, currency, date and time, etc. The value of the attribute must be available in the corresponding database..

Primary Integrity Constraints

The primary/entity integrity says that every table must have a primary key and column or columns chosen for the primary key should be unique or not null. The Primary key is used to identify individual row or record from the entity. If the primary key has a NULL value, then we can’t identify those rows or records.

Referential Integrity Constraints

A referential integrity constraint concerns the concept of a foreign key.  It states that any foreign-key value can only be in one of two states as the primary key of the relative table or null value. Null represents no relation between table.

Key Constraints

Keys constraints are the entity set rule that is used to identify a record within its entity uniquely. An entity can have multiple keys, but out of which one key will be the primary key as given above in Primary Integrity Constraints.

Types of Data Integrity

The following types of data integrity rules applied by developers or data administrators on  tables :

• Nulls:  Define a rule for the column to allows or disallows Null value in inserts or updates of rows. When referenced type column data is updated or deleted, all associated columns data is set to NULL.
• Unique Column Values: Define a rule on a column or set of columns that allow only insert or update of a row with a unique value for the column or set of columns.
• Primary Key Values: Define a rule on a column or set of columns so that each row or record in the table can be uniquely identified by the values in the column or set of columns.
• Referential Integrity: Define a rule on a column or set of columns in a table that allows the insert or update of a record only if the value for the column or set of columns (foreign key value) matches a value in a column of a related table (the referenced value/ primary key value).
• Restrict: A referential integrity rule that restricts the update or deletion of referenced data.
• Set to Default: When referenced column data is updated or deleted, all associated dependent column data is set to a default value.
• Cascade: When referenced column data is updated, all associated dependent column data is correspondingly updated; when a referenced row/record is deleted, all associated dependent rows/records are deleted.
• Database triggers: Define rules to invoke triggers when particular column or table perform operations insert, update or delete and called procedures. Triggers used in the following situations:
  • Update or delete dependent data when above referential integrity rules not able to handle. For Ex:  update and delete SET NULL, update and delete SET DEFAULT or update CASCADE.
  • Call the procedure from different nodes (distributed database) when child and parent tables are on different nodes.
  • Apply business rules apart from the above data integrity constraints.

   

References

https://docs.oracle.com/cd/A57673_01/DOC/server/doc/SCN73/ch7.htm