Enable/ Disable Constraints

The best way to insert the valid data by using constraints. This also known as data enforcing data or data integrity. We already know the constraints in sql server.diffrent types of constraints in sql server. They are.

1. Primary key constraint.
2. Default key Constraint.
3. Check Constraint.
4. Foreign key Constraint
5. Unique key Constraint

Example:

create table Student

(

ID int primary key,

Name varchar(20) unique ,

Marks int not null check (marks>500),

SchoolName varchar(20) default 'HydPublic'

)

Senario-1:

insert into Student(ID,Name,Marks) values(1,'rakesh',550)

If we check the folliowing data from Student table

select * from Student

Senario-2:

insert into Student(ID,Name,Marks) values(2,'nagaraju',350)

If we are trying to insert second record with marks 350 values we ever run above insert query it gives error because.

The INSERT statement conflicted with the CHECK constraint "CK__Student__Marks__29572725". The conflict occurred in database "Demo", table "dbo.Student", column 'Marks'.

How to insert 350 marks into marks column ?

Ans:

We need to disable the Check Constraint on table.

How can we do this stuff?

Ans:

By using alter statement.

Synatx:

Alter table ‘tablename’ nocheck constraint ‘Check_Constraintname’

Exmaple:

alter table Student nocheck constraint CK__Student__Marks__29572725

insert into Student(ID,Name,Marks) values(2,'nagaraju',350)

Try to insert the above record . we we run above insert query the record get’s inserted successfully.because of check constraint is diabled on this time.

Check the data

select * from Student

How to enable check constraint on table ?

alter table Student check constraint CK__Student__Marks__29572725

try to insert the the invalid data on Marks column and see the result.

Senario-3:

create table SchoolName

(

ID int identity(1,1) primary key,

Name varchar(20) unique

)

Drop table Student

create table Student

(

ID int primary key,

Name varchar(20) unique ,

Marks int not null check (marks>500),

SchoolName int foreign key references SchoolName(ID)

)

I have created the 2 tables SchoolName table is Just like Master table . it is maintaining tte All school Name Information on this table. Student table is maintaining the Schools information. In student table SchoolName columns is references to the ID column in SchoolName table.

Insert Data to SchoolName table

insert into SchoolName

select 'HydPublic'

union all

select 'BabyMoon'

Select ScoolName table

select * from SchoolName

insert into Student(ID,Name,Marks,SchoolName) values(1,'rakesh',550,1)

Run above query.record get inserted successfully.

insert into Student(ID,Name,Marks,SchoolName) values(2,'phani',550,3)

Run above query it throws an error.beacause of SchoolName value 3 deos not exists in ID column of SchoolName table.

The INSERT statement conflicted with the FOREIGN KEY constraint "FK__Student__SchoolN__4222D4EF". The conflict occurred in database "Demo", table "dbo.SchoolName", column 'ID'.

insert into Student(ID,Name,Marks,SchoolName) values(2,'phani',550,3)

How Run above Insert Query Successfully ?

Ans:

To disable the FOREIGN KEY Constraint from Student table.

How can we do this stuff?

Ans:

By using alter statement.

Syntax:

Alter table ‘tablename’ nocheck constraint ‘FOREIGN KEY CONSTRAINT NAME’

Example:

alter table Student nocheck constraint 

FK__Student__SchoolN__4222D4EF

Try insert record.

insert into Student(ID,Name,Marks,SchoolName) values(2,'phani',550,3)

Here record get insereted successfully .

How to enable FOREIGN KEY Constraint?

alter table Student check constraint FK__Student__SchoolN__4222D4EF

After enable try insert below inavlid record ans check result.

insert into Student(ID,Name,Marks,SchoolName) values(3,'Dan',550,3)

Is it possible disable and enable the all constraint from a table ?

Ans:

Yes.

Example:

Disable:

alter table Student nocheck constraint all

Enable :

alter table Student check constraint all

Limitations:

• Enabing , disabling constraints works only check constraint and foreign key constraint.
• Enabing , disabling constraints does not works default,primary, unique constraint.