What is Data Integrity

Data integrity ensures that the data which is going to enter in the database is reliable, accurate and consistent. Data integrity maintains the correctness and completeness of data.

What is Constraints

To maintain the data integrity, we use the constraints. Constraints are the rules which we applied on the table’s column to ensure the data integrity.

We can add constraints on column while creating the table and can also add after creating the table.



Type of Constraints

Following are the types of constraints: -

Primary Key

Primary key is a constraint which is used to uniquely identify the rows in a table. When we define a column as primary key then this column does not take the repeated value and also not take the null.

Example of Primary Key


		
create table Course(Course_Id int primary key, Course_Name varchar(100));
or

create table Course(Course_Id int Constraint CPk primary key(Course_Id), Course_Name varchar(100));

or

create table Course(Course_Id int Constraint CPk primary key, Course_Name varchar(100));
or

create table Course(Course_Id int , Course_Name varchar(100) Constraint CPk primary key(Course_Id));

We can add primary constraints while creating the table and after creating the table.

Note: -if we create primary key after creating the table then it checks the data in this column. If any data in this columns is repeating or null, then the request of creating primary key has been cancelled.




Unique Key

Unique constraints are used to create unique column in database which is non primary key. A difference between primary key and unique key is that primary key doesn’t except null but in unique one value can be null.

Multiple unique constraints can be created in single table.

Example of Unique


		
create table t1(id int primary key, phone varchar(100) unique not null);
or
create table t2(id int primary key, phone varchar(100) constraint unk unique);
or
create table t3(id int primary key, phone varchar(100), 
email varchar(200) constraint unkphone unique(phone), constraint unkemail unique(email));

		



Foreign key

A foreign key constraint is used when the data of one table depends on the data in another table.

A foreign key constraint can be applicable to more than one column in table the only condition is that, that column should follow the primary key constraints in another table. To create foreign key, we have to give the reference of table from where this key is following the primary key constraints.

Example of Foreign Key


		
create table t3(id int primary key, phone varchar(100), 
email varchar(200) constraint unkphone unique(phone), constraint unkemail unique(email));

create table t4(id int foreign key references t3(id), fees int);

or

create table t5(id int, headid int constraint  fkid foreign key(id) references t3(id),constraint  fkhead foreign key(headid) references t3(id));

		



Default Constraints

A default constraint is used when we need to enter some predefined value in the column so that user needs not to enter any value for this column.


		
create table t6(id int, fees int, ispaid char(1) default 'n');
		


Check Constraints

When we want to make a particular column to be restricted for particular data in that case we use check constraints.


		
create table t7(id int, gender char(1) check(gender in ('M','F')));
or
create table t8(id int, age int check(age>36));