Database integrity constraints and its types with example

Data Integrity

When we are designing a database, there is lot of factors to be concentrated on. We need to make sure that all the required datas are distributed among right tables and there is no duplication/missing data. The space utilised for the appropriately for the database. Time taken for each query is minimal and so on.

Data integrity is the maintenance of, and the assurance of the accuracy and consistency of, data over its entire life-cycle,^[1] and is a critical aspect to the design, implementation and usage of any system which stores, processes, or retrieves data.

Data integrity is usually imposed during the database design phase through the use of standard procedures and rules. Data integrity can be maintained through the use of various error-checking methods and validation procedures.

Many commercial database management systems have an integrity subsystem, which is responsible for monitoring transactions, which update the database and detecting integrity violations. In the event of an integrity violation, the system then takes appropriate action, which should involve rejecting the operation, reporting the violation, and if necessary returning the database to a consistent state.

Integrity rules may be divided into three 'broad categories:

• Domain integrity rules

• Entity integrity rules

• Referential integrity rules

Domain integrity rules are concerned with maintaining the correctness of attribute values within relations. A domain integrity rule therefore, is simply a definition of the type of the domain, and domain integrity is closely related to the familiar concept of type checking in programming languages

Example: If we have an attribute AGE, it is not sufficient to describe its type as INTEGER since this does not prevent unrealistic values for AGE (e.g. negative values) being entered into the database. At the very least we should be able to specify that the domain type for attribute AGE is POSITIVE_INTEGER, and ideally it should be possible to specify upper and lower bounds for values of AGE. Unfortunately commercial database management 'systems typically provide only simple types for domains. For example, the ORACLE database management system provides the domain types: NUMBER, CHAR (variable length character strings), DATE and TIME. INGRES and DB2 provide similar restricted domain types.

Entity integrity rules

This integrity ensures that each record in the table is unique and has primary key which is not NULL.  That means, there is no duplicate record or information of data in a table and each records are uniquely identified by non null attribute of the table.

In a STUDENT table, each student should be a different from other and there will not be duplicate records. Also, STUDENT_ID which is a primary key in the table has non-null values for each of the record.

Requirement of entity integrity rules: All entries are unique and no null entries in a primary key.

Purpose of Entity identity rules: Guarantees that each entity will have a unique

Referential Integrity: This is the concept of foreign keys. The rule states that the foreign key value can be in two states. The first state is that the foreign key value would refer to a primary key value of another table, or it can be null. Being null could simply mean that there are no relationships, or that the relationship is unknown

Referential integrity rules are concerned with maintaining the correctness and consistency of relationships between relations.

Purpose of Referential integrity rules: Makes it possible for an attribute NOT to have a corresponding value, but it will be impossible to have an invalid entry. The enforcement of the referential integrity rule makes it impossible to delete a row in one table whose primary key has mandatory matching foreign key values in another table.

Example: Examples of referential integrity constraint in the Customer/Order database of the Company:

·         Customer(CustID, CustName)

·         Order(OrderID, CustID, OrderDate)

·         To ensure that there are no orphan records, we need to enforce referential integrity. An orphan record is one whose foreign key FK value is not found in the corresponding entity – the entity where the PK is located. Recall that a typical join is between a PK and FK.

·         The referential integrity constraint states that the customer ID (CustID) in the Order table must match a valid CustID in the Customer table. Most relational databases have declarative referential integrity. In other words, when the tables are created the referential integrity constraints are set up.