Explain about Integrity Constraints with examples

Integrity Constraints

Implement the database tables, one must define the integrity constraints. Integrity means something like 'be right' and consistent. The data in a database must be right and in good condition.
There are the domain integrity, the entity integrity, the referential integrity

Domain Integrity

Domain integrity means the definition an attribute. You define 
- data type,- lenght or size- is null value allowed- is the value unique or not for an attribute.
You may also define the default value,

Entity Integrity Constraint

The entity integrity constraint states that primary keys can't be null. There must be a proper value in the primary key field.
This is because the primary key value is used to identify individual rows in a table. If there were null values for primary keys, it would mean that we could not indentify those rows.
On the other hand, there can be null values other than primary key fields. Null value means that one doesn't know the value for that field. Null value is different from zero value or space.
The entity integrity constraints assure that a specific row in a table can be identified. 

Referential Integrity Constraint

The referential integrity constraint is specified between two tables and it is used to maintain the consistency among rows between the two tables.
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.
Constraints are the rules that the table must satisfy. These can be specified at the time of creating table or can also be added to table by using alter table statement.

·        Constraints prevent the table from deletion, if there is any dependency

• DEFAULT Constraint
• NOT NULL Constraint
• UNIQUE Constraint
• CHECK Constraint
• PRIMARY KEY Constraint
• FOREIGN KEY Constraint

·         Create table table_name    (column1 datatype column1 constraint,         column2 datatype column2 constraint,     column3 datatype);

Constraints can be defined at 2 levels :

·         Column level – At column level the constraints are referred for a     single column and is defined within the specification.

·         Table Level – At table level when constraints are defined at table   level then it can be referred to one or more columns and it can be     defined separately from the definitions of the columns in the table.

DEFAULT CONSTRAINT

It is used to define a default value for an attribute.

CREATE TABLE employee (  EID int,  Name varchar(20),  

Address varchar(50),  Salary int,  

Sex char(1) DEFAULT='M'  );NOT NULL Constraint

·        NOT NULL constraint is used when we do not want null values to be entered. It ensures that null values are not permitted in the column.

·        The columns without NOT NULL constraint can have null values. By default, a column can contain NULL.

Example :

CREATE TABLE employee  (EmpID int NOT NULL,ame varchar(30) 

NOT NULL,         Address varchar(50),  Salary int, Sex char(1)

 DEFAULT='M' );

UNIQUE Constraint

·         The UNIQUE constraint is used to uniquely identify each record in a          database. It requires that each value in a column should be    unique,          i.e. no 2 rows of same column should have duplicate values.

·         It provides you the guarantee for uniqueness in a column or set of columns and allows us to input the NULL values, unless there is not   defined NOT NULL constraint for the same column.

It can be defined at table level or at column level.

·         CREATE TABLE employee (EID int NOT NULL, Name varchar(20) 

NOT NULL,

·           Address varchar(50),  Salary int,  Sex char(1) DEFAULT='M',

·           UNIQUE (EID)  );

CHECK constraint :

·         The check constraint is used to check the value which is entered into       a      record. It is used to define condition which each row must satisfy.

·         If the condition value evaluates to fall, then the record violates the   constraint and you cannot enter it into the table.

·         We cannot define check constraint in SQL view.

Syntax :

CREATE TABLE table_name (   column1 datatype,   column2 datatype,

   ....

   CONSTRAINT constraint_name CHECK   (column-name_contition));

The check constraint can be defined by a create table statement or alter table statement.

CREATE TABLE employee (  EmpID int NOT NULL,  Name varchar(30) 

NOT NULL,  Address char(50),  Sex char(1) DEFAULT='M', 

 Salary int,  PRIMARY KEY(EmpID),  

 CONSTRAINT chk_Salary CHECK (Salary>20000) );

CHECK Constraint using ALTER TABLE Statement :

Add a CHECK Constraint :

Syntax :

ALTER TABLE table_nameADD CONSTRAINT constraint_name CHECK

(column_name condition);

Example :

ALTER TABLE employeeADD CHECK (EmpID>999);

DROP a CHECK Constraint

Syntax :

ALTER TABLE table_nameDROP CONSTRAINT constraint_name;

Example :

ALTER TABLE employeeDROP CONSTRAINT chk_EmpID;

Primary key constraint

A primary key constraint is a field in a table which uniquely identifies each rows/records in a database table.

Characteristics of Primary Key :

·         Primary key constraint must contain unique values.

·         Primary key constraint column cannot have null values.

·         Table can have only one primary key.

·         If multiple fields are used as a primary key, then they are called as          composite key.

Example :

CREATE TABLE employee     (EmpID int NOT NULL,       

  Name varchar(30) NOT NULL,     Address varchar(50),        Salary int,

         Sex char(1) DEFAULT='M',         PRIMARY KEY (EmpID));

ADD or DROP a PRIMARY KEY using ALTER TABLE Statement :

Alter table statement is used, if we want to add or drop constraint later.

Add a primary key

Syntax

ALTER TABLE table_nameADD CONSTRAINT constraint_name

 PRIMARY KEY (col1, col2,....,coln);

Example :

ALTER TABLE employeeADD CONSTRAINT employee_pk PRIMARY 

KEY (EmpID);

By using alter table statement, you can add PRIMARY KEY constraint on the existing table employee named employee_pk.

DROP a Primary Key :

Syntax :

ALTER TABLE table_nameDROP CONSTRAINT constraint_name;

ALTER TABLE employeeDROP CONSTRAINT employee_pk;

 

Foreign key constraint

·         If foreign key is a field which points to the primary key of another     table. Means that, the same value in one table must also appear in     another table.

·         The reference table, means the table which contains primary key is          called the parent table and the table with the foreign key is called the         child table.

·         If foreign key field cannot contain values, which are not present in the      parent tables. In other words, it is used to enforce referential integrity          between tables in a relational database.

·         Primary key field cannot contain null values but foreign key field can        contain null values.

·         Foreign key constraint can be defined by a CREATE      TABLE statement or an ALTER TABLE statement.

A foreign key constraint can refer to the columns of tables in the same database for within the same table.

Syntax :

CREATE TABLE table_name (  column1 datatype,  column2 datatype,

   CONSTRAINT fk_column  FOREIGN KEY (column1,column2,....)

  REFERENCES parent_table (column1,column2,.....) );

PRIMARY KEY table : "employee" as defined above.