Data integrity in SQL ensures that the data is complete and correct. Data integrity constraints are used for restricting certain values that can be added or modified in a database. Here are some of the data integrity constraints in SQL:
1) Required data values: There are some values in a table which are required and cannot be NULL. For Example, EMPL_NAME or EMPL_NUM is a required column in a SALARY table. As for each salary or perks, there has to be an associated employee. A column can be declared NOT NULL. When you declare a column as NOT NULL, for each INSERT and UPDATE statement there has to be a non NULL data value for the column. A NOT NULL must be declared when a table is created. It cannot be done for existing table.
2) Entity integrity: Every table must have a unique primary key. This primary key can’t be null. When a primary key is specified, it’s uniqueness is checked automatically.
3) Referential integrity: Foreign key can refer to primary key value of a table and it can be null.
4) Domain integrity: Every column in a database must have a domain having values of same type. A domain concept simplifies the operations in a table. It also stores values which can be accessed easily.
5) Business rules and transactions: There can be business and transaction constraints for a particular database. For Example, an order cannot be accepted for items which has limited inventory.
6) Consistency: Adding and updating rows should be in a consistent manner.
7) Column check constraints: A check constraint is a search condition. A value is checked and for each TRUE a row/s is inserted by using INSERT or updated by using UPDATE statement.
8) Uniqueness constraints: A column which is not primary key of a table can have all unique values in each row.
9) Assertion: It is used for specifying a relationship between data values which crosses multiple tables with in the database. The disadvantage of assertion is that, it’s processing overhead is high.
10) Other constraints: There can be other constraints to govern other relationships between data items. For Example, there can be constraints for perks of the employees.