INTEGRITY CONSTRAINTS OVER RELATION

• Database integrity refers to the validity and consistency of stored data. Integrity is usually expressed in terms of constraints, which are consistency rules that the database is not permitted to violate. Constraints may apply to each attribute or they may apply to relationships between tables.

• Integrity constraints ensure that changes (update deletion, insertion) made to the database by authorized users do not result in a loss of data consistency. Thus, integrity constraints guard against accidental damage to the database.

EXAMPLE- A brood group must be ‘A’ or ‘B’ or ‘AB’ or ‘O’ only (can not any other values else).

TYPES OF INTEGRITY CONSTRAINTS

Various types of integrity constraints are-

1. Domain Integrity

2. Entity Integrity Constraint

3. Referential Integrity Constraint

4. Key Constraints

1. Domain Integrity-

Domain integrity means the definition of a valid set of values for an attribute. You define data type, length or size, is null value allowed , is the value unique or not for an attribute ,the default value, the range (values in between) and/or specific values for the attribute.

2. Entity Integrity Constraint-

This rule states that in any database relation value of attribute of a primary key can't be null.

EXAMPLE- Consider a relation "STUDENT" Where "Stu_id" is a primary key and it must not contain any null value whereas other attributes may contain null value e.g "Branch" in the following relation contains one null value.

3.Referential Integrity Constraint-

It states that if a foreign key exists in a relation then either the foreign key value must match a primary key value of some tuple in its home relation or the foreign key value must be null.

The rules are:

1. You can't delete a record from a primary table if matching records exist in a related table.
2. You can't change a primary key value in the primary table if that record has related records.
3. You can't enter a value in the foreign key field of the related table that doesn't exist in the primary key of the primary table.
4. However, you can enter a Null value in the foreign key, specifying that the records are unrelated.

EXAMPLE-

Consider 2 relations "stu" and "stu_1" Where "Stu_id " is the primary key in the "stu" relation and foreign key in the "stu_1" relation.

Relation "stu"

Relation "stu_1"

Examples

Rule 1. You can't delete any of the rows in the ”stu­” relation that are visible since all the ”stu” are in use in the “stu_1” relation.

Rule 2. You can't change any of the ”Stu_id” in the “stu” relation since all the “Stu_id” are in use in the ”stu_1” relation. * Rule 3.* The values that you can enter in the” Stu_id” field in the “stu_1” relation must be in the” Stu_id” field in the “stu” relation.

Rule 4 You can enter a null value in the "stu_1" relation if the records are unrelated.

4.Key Constraints-

A Key Constraint is a statement that a certain minimal subset of the fields of a relation is a unique identifier for a tuple. The types of key constraints-

1. Primary key constraints
2. Unique key constraints
3. Foreign Key constraints
4. NOT NULL constraints
5. Check constraints

1. Primary key constraints

Primary key is the term used to identify one or more columns in a table that make a row of data unique. Although the primary key typically consists of one column in a table, more than one column can comprise the primary key.

For example, either the employee's Social Security number or an assigned employee identification number is the logical primary key for an employee table. The objective is for every record to have a unique primary key or value for the employee's identification number. Because there is probably no need to have more than one record for each employee in an employee table, the employee identification number makes a logical primary key. The primary key is assigned at table creation.

The following example identifies the EMP_ID column as the PRIMARY KEY for the EMPLOYEES table:

CREATE TABLE EMPLOYEE_TBL
(EMP_ID        CHAR(9)        NOT NULL PRIMARY KEY,
EMP_NAME       VARCHAR (40)   NOT NULL,
EMP_ST_ADDR    VARCHAR (20)   NOT NULL,
EMP_CITY       VARCHAR (15)   NOT NULL,
EMP_ST         CHAR(2)        NOT NULL,
EMP_ZIP        INTEGER(5)     NOT NULL,
EMP_PHONE      INTEGER(10)    NULL,
EMP_PAGER      INTEGER(10)    NULL);

2. Unique Constraints

A unique column constraint in a table is similar to a primary key in that the value in that column for every row of data in the table must have a unique value. Although a primary key constraint is placed on one column, you can place a unique constraint on another column even though it is not actually for use as the primary key.

CREATE TABLE EMPLOYEE_TBL
(EMP_ID        CHAR(9)        NOT NULL     PRIMARY KEY,
EMP_NAME       VARCHAR (40)   NOT NULL,
EMP_ST_ADDR    VARCHAR (20)   NOT NULL,
EMP_CITY       VARCHAR (15)   NOT NULL,
EMP_ST         CHAR(2)        NOT NULL,
EMP_ZIP        INTEGER(5)     NOT NULL,
EMP_PHONE      INTEGER(10)    NULL         UNIQUE,
EMP_PAGER      INTEGER(10)    NULL)

3. Foreign Key Constraints

A foreign key is a column in a child table that references a primary key in the parent table. A foreign key constraint is the main mechanism used to enforce referential integrity between tables in a relational database. A column defined as a foreign key is used to reference a column defined as a primary key in another table.

CREATE TABLE EMPLOYEE_PAY_TBL
(EMP_ID           CHAR(9)        NOT NULL,
POSITION           VARCHAR2(15)  NOT NULL,
DATE_HIRE          DATE          NULL,
PAY_RATE           NUMBER(4,2)   NOT NULL,
DATE_LAST_RAISE    DATE          NULL,

4. NOT NULL Constraints

Previous examples use the keywords NULL and NOT NULL listed on the same line as each column and after the data type. NOT NULL is a constraint that you can place on a table's column. This constraint disallows the entrance of NULL values into a column; in other words, data is required in a NOT NULL column for each row of data in the table. NULL is generally the default for a column if NOT NULL is not specified, allowing NULL values in a column.

5. Check Constraints

Check (CHK) constraints can be utilized to check the validity of data entered into particular table columns. Check constraints are used to provide back-end database edits, although edits are commonly found in the front-end application as well. General edits restrict values that can be entered into columns or objects, whether within the database itself or on a front-end application. The check constraint is a way of providing another protective layer for the data.

CREATE TABLE EMPLOYEE_TBL
(EMP_ID        CHAR(9)        NOT NULL,
EMP_NAME       VARCHAR2(40)   NOT NULL,
EMP_ST_ADDR    VARCHAR2(20)   NOT NULL,
EMP_CITY       VARCHAR2(15)   NOT NULL,
EMP_ST         CHAR(2)        NOT NULL,
EMP_ZIP        NUMBER(5)      NOT NULL,
EMP_PHONE      NUMBER(10)     NULL,
EMP_PAGER      NUMBER(10)     NULL),
PRIMARY KEY (EMP_ID),
CONSTRAINT CHK_EMP_ZIP CHECK ( EMP_ZIP = '46234');