DATA INTEGRITY CONSTRAINTS

Data integrity refers to the overall completeness, accuracy and consistency of data.

Data Integrity Constraints are the rules enforced on data columns on table.

These are used to limit the type of data that can go into a table.

Constraints could be column level or table level.

Column level constraints are applied only to one column where as table level constraints are applied to the whole table.

In other words, Constraint is a mechanism used by databases to prevent invalid data entry into the table.

The following are various types of integrity constraints:-

§  Domain Integrity Constraint

§  Entity Integrity Constraint

§  Referential Integrity Constraint


Domain Integrity Constraint

These constraints set a range, and any violations that take place will prevent the user from performing the manipulation that caused the breach.

There are basically two types of Domain Integrity Constraint. These are:

§  Not Null Constraint

§  Check Constraint

Not Null Constraint:

§  We know that by default all columns in a table allow Null values. This does not mean that column should compulsory be NULL.

§  The database allows null values without returning an error.

§  When a NOT NULL Constraint is enforced though, either on a column or set of columns in a table, it will allow Null values.

§  The user has to provide a value for the column.

§  NOT NULL Integrity constraint can be defined using alter table command even when the table contains row.

§  Zero and Null are not equivalent

§  One Null is not equivalent to another Null

Example:

SQL> create table emp(empname varchar(10)contraint n1 not null, roll no number(10) not null);


Check Constraint:

§  The CHECK constraint ensures that all values in a column satisfy certain conditions.

§  These rules are governed by logical expressions or Boolean Expressions.

§  Check constraints cannot contain sub queries.

Example:

SQL> create table emp(empname varchar(10)contraint n1 not null, roll no number(10) constraint c1 check(roll no<50));


Entity Integrity Constraint

Any entity is any data recorded in a database. Each entity represents a table and each row of a table represents an insurance of that entity.

Each row in a table can be uniquely identified using the entity constraint.

§  Unique Constraint

§  Primary Key Constraint

Unique Constraint:

§  The unique value rule/constraint means that each value in a particular column is unique, such as DEPTNO in DEPT table.

§  The database rejects duplication of records when the unique key constraint is used.

§  When a NOT NULL Constraint is enforced though, either on a column or set of columns in a table, it will allow Null values.

§  The user has to provide a value for the column.

§  NOT NULL Integrity constraint can be defined using alters table command even when the table contains row.

§  Zero and Null are not equivalent

§  One Null is not equivalent to another Null

§  Usage of the unique key constraint is to prevent the duplication of values within the rows of a specified column or set of columns in a table.

§  Columns defined with this constraint can also allow Null values. If Unique key constraint is defined in more than one column that combination of columns, it is called composite unique key.

§  In the above case, combination of columns cannot be duplicated.

§  Maximum combination of columns that a composite unique key can contain is 16.

Example:

Primary Key Constraint:

§  The primary key constraint specifies that each row of a table must be identified by a unique value.

§  It is almost similar to unique key constraint.

§  The primary key constraint avoids duplication of rows and does not allow Null values, when enforced in a column or set of columns.

§  A table can have only one primary key.

§  If a primary key constraint is assigned to a combination of columns it is said to be a composite primary key, which can contain a maximum of 16 columns.

Example:


Referential Integrity Constraint

To establish a parent-child or a master-childrelationship between two tables having a common column, we make use of referential integrity constraints.

To implement this, we should define the column in the parent tables as primary key and the same column in the child table as a foreign key referring to the corresponding parent entry.

Basic concepts related to Referential Integrity are:

§  Foreign Key

§  Referenced Key

Foreign Key:

§  A column or combination of columns included in the definition of referential integrity, which would refer to a reference key.

Referenced Key:

§  It is a unique or primary key, which is defined on a column belonging to the parent table.

Child Table:

§  This table depends upon the values present in the referenced key of the parent table, which is referenced by a foreign table.

Parent Table:

§  This table determines whether insertion or updation of the data can be done in the child table.

§  This table would be referred by child’s table foreign key.

Advertisements

Give A message for us

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s