Maintaining Data Integrity in Sql

Data integrity tells about marinating the proper data. To maintain proper data we need to impose some business rules on data. The business rules are provide in the from at data integrity constraints which are going to restrict us,  If we don't maintain the integrity.

1. Not null
2. Unique
3. Primary Key
4. Default
5. Check
6. Foreign Key

1 -  Not Null

If this constraint is imposed on a column it will not allow null values on it. We can impose 'n' number of not null constraints on a table.

Syntax

Create Table <Table Name> (Col1 <Data Type>[Width][Not Nulll],Col2 <Data Type>[Width][Not Nulll],.........................Coln <Data Type>[Width][Not Nulll]

Example

Create Table Employee(empid int Not Null, ename varchar(50), salary decimal(7,2)  Not Null

If you try to insert values in Employee table like

Insert into Employee(101,'Sharad',NULL) ->ERROR

Note

The draw of Not Null constraints is even if it restricts null values. It will also allow duplicate values.

2 -  Unique

If this constraint is impose on a column, It will not allow the duplicate values.

Example

Create Table Employee(empid int Unique, ename varchar(50), salary decimal(7,2)  Not Null

Note

• The not null constraint is a part of the table, which  stores along with column in the table and when constraint is violated it will display us the name of the constraint on which the violation has been done.
• The rest of constraints are separated object in the database apart from table and linked up the column because they are separated jobs they have their own name for identification. So if these constraints are violated they display their own identity but not the name of column.
• So when this constraint are violated using the name only we need to identity where the violation has been done. Now follow the following convention and provide name to constraints.

Example

Create Table Employee(empid int constraint empid Unique, ename varchar(50), salary decimal(7,2)  Not Null

A constraint can be defined or apply in 2 ways

1. Column level definition
2. Table level definition

1 - Column level definition

In this case the details of constraints will provide immediately after column details as if in our previous example.

2 - Table level definition

In this case first all the columns are define, and in the end we define the constraint of the column.

Example

Create Table Employee(empid int , ename varchar(50), salary decimal(7,2)  Not Null, constraint empid_uq Unquie(empid))

Note

There is no different in behavior however we define the constraint either in table level or column level but the advantage is it, A constraint is defined at table level we can go for a composite constraint that is single on multiple columns. Basically the composite unique constraint checks the uniqueness the combination of columns but not single column.

Example

Create Table Employee_Deatils(city_code varchar(10),area_code varchar(10), address varchar(40), constraint cc_ac_uq  unique (city_code,area_code))

Note

The drawback of the unique constraint is it allow null value on it.

3 - Primary Key

This constraint is a combination of unique and not null, which restricts duplicate values as well as null values also. A table can have only one primary key constraint present on it, which can be either single column, or multiple columns.

Example

Create Table Employee(empid int constraint eid primary key, ename varchar(50), salary decimal(7,2)  Not Null

Composite Primary Key Example

Create Table Employee_Deatils(city_code varchar(10),area_code varchar(10), address varchar(40), constraint cc_ac_pkprimary key(city_code,area_code))

4 - Default Constraint

The default value of any column in a table is null unless a not null constraint or primary key imposed on it. We can change the default value of a column according to our need by using default constraint. The value which is specified as a default value comes into the picture when no value is specified to the column while inserting data into table.

Example

Create Table Employee(empid int , ename varchar(50), salary decimal(7,2)  default 2000)

When you insert a row into table and does not provide salary column value it automatically insert 2000 in salary column.

5 - Check Constraint

This is used for checking the value within a common to be according specification as following

• Check (salary>=2000)
• Check (salary >= 2000 And salary<=5000)
• Check (salary ==2000 Or salary=5000)

Example

Create Table Employee(empid int , ename varchar(50), salary decimal(7,2)  Not Null constraint sal_ck Check(salary>=30000))

6 - Referential Integrity Constraint

This constraint is used for establishing master child relation between tables. To establish the relationship we require the following.

• Master Table  :-  Which is going to contain the initial information in it.
• Child Table : - Which should contain the detail information in it.
• A reference key column in the master table which provide the reference values for the child table. This provides the reference values for the child table. This reference key column should contain either a primary key constraint or a unique constraint on it.
• A foreign key column on a child table, which should restrict the values into the column, that are not present in the reference key column for doing this it has been imposed with a foreign key constraint on it.