Implements Constraints in Table in SQL SERVR

Written By:- Isha Malhotra (Software Developer)
Email:-malhotra.isha3388@gmail.com.
Note: -if you find this article helpful then kindly leave your comment

For Asp.net Training with C# and Sql Server Click Here

Implements Constraints in Table in SQL SERVR

Or

Maintain Data Integrity in SQL SERVER

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.

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.

Following are the types of constraints:-

1.     Primary Key Constraints

Primary key is a constraint which is used to uniquely identified 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.

For Example:-

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 check 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.

2.       Unique Constraints

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.

For Example

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));

3.       Foreign key constraints

Foreign key constraints is used when the data of one table depends on the data in another table.

A foreign key constraints 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.

For example

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));

4.       Default Constraints

Default constraints is used when we need to enter some predefined value in the column so that user need not to enter any value for this column.

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

5.       Check Constraints

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

For example

create table t7(id int, gender char(1) check(gender in ('M','F')));

or

create table t8(id int, age int check(age>36));

Practical work

Implements all constraints in table and try to insert the data according to the constraints rules.