Database Integrity Constraints

Data integrity is the maintenance of, and the assurance of the accuracy and consistency of data. Data integrity is the accuracy, completeness, and reliability of data throughout its life cycle. 

Three types of integrity constraints are an inherent part of the relational data model: 

• Entity integrity, 
• Referential integrity and 
• Domain integrity.

Entity Integrity

Entity Integrity is the mechanism the system provides to maintain primary keys. The primary key serves as a unique identifier for rows in the table. Entity Integrity ensures two properties for primary keys:

• The primary key for a row is unique; it does not match the primary key of any other row in the table.
• The primary key is not null, no component of the primary key may be set to null.

The uniqueness property ensures that the primary key of each row uniquely identifies it; there are no duplicates. The second property ensures that the primary key has meaning, has a value; no component of the key is missing.

The system enforces Entity Integrity by not allowing operations (INSERT, UPDATE) to produce an invalid primary key. Any operation that creates a duplicate primary key or one containing nulls is rejected.

Referential Integrity

Referential Integrity is the mechanism the system provides to maintain foreign keys. The definition of a foreign key must specify the table whose primary key is being referenced. Referential Integrity ensures only one property for foreign keys:

• A valid foreign key value must always reference an existing primary key or contain a null

A foreign key may contain a null; it does not need to reference an existing primary key (actually, it can't reference any primary key since primary keys cannot contain nulls).

While the Referential Integrity property looks simpler than those for Entity Integrity, the consequences are more complex since both primary and foreign keys are involved. The rule for foreign keys is:

• No operation (INSERT, UPDATE) can create a non-null foreign key unless a corresponding primary key exists.

Any operation that produces a non-null foreign key value without a matching primary key value is rejected. Primary keys are also constrained by Referential Integrity:

• No operation (UPDATE, DELETE) can remove or change a primary key while a referencing foreign keys exist.

Here, the related table contains a foreign key value that doesn’t exist in the primary key field of the primary table (i.e. the “CompanyId” field). This has resulted in an “orphaned record”.

Domain Level Integrity

A domain defines the possible values of an attribute. Domain Integrity rules govern these values. In a database system, the domain integrity is defined by:

• The datatype and the length
• The NULL value acceptance
• The allowable values, through techniques like constraints or rules
• The default value.

Apart from this there are constraints on a single relationsuch as

1. not null

2. unique

3. check(<predicate>)

NOT NULL

The NOT NULL constraint enforces a column to NOT accept NULL values. Any modification to database that would cause a null to be inserted in an attribute declared to be not null generates an error.

UNIQUE

The UNIQUE constraint ensures that all values in a column are different.

Both the UNIQUE and PRIMARY KEY constraints provide a guarantee for uniqueness for a column or set of columns.

A PRIMARY KEY constraint automatically has a UNIQUE constraint.

CHECK

The CHECK constraint is used to limit the value range that can be placed in a column.

If a CHECK constraint is defined on a column it will allow only certain values for this column.

If a CHECK constraint is defined on a  table it can limit the values in certain columns based on values in other columns in the row.

Ex:

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    CHECK (Age>=18)
);