Problem: What is INTEGRITY in a DB2? Explain DOMAIN, ENTITY and REFERENTIAL integrity with the help of an example in ORDERS table.
The integrity refers to the accuracy, consistency and correctness of data present in the DB2 database. Data integrity is imposed during the database design to make sure that data residing in the database remains complete, accurate and reliable.
There are three types of integrity described in below figure.
It ensures that column data in the DB2 table adhere to the permissible set of values. For example, in the ORDERS table, the domain integrity on the ORDER_DATE column ensures that data in this column is always in DATE format.
It ensures that each row in a DB2 table is unique. This integrity is achieved using the primary key. For example, the ORDERS DB2 table has a primary key as ORDER_ID, which cannot be duplicated throughout the table.
It ensures that data integrity is maintained between primary and foreign key. For example, ORDERS DB2 table has a primary key as ORDER_ID and foreign key as TRANSACTION_ID which refers to the TRANSACTIONS DB2 table.