Data integrity constraints in SQL

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.

Leave a Reply