Enable/Disable Constraints in SQL Server

The best way to insert valid data is with constraints. This is also known as data enforcing data or data integrity. We already know that there are various 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'
)

Scenario 1

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

If we check the folliowing data from the Student table:

select * from Student

 

 

Scenario 2

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

If we are trying to insert a second record with the marks of a value of 350 then when we run the above insert query it gives an 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

 

We need to disable the Check Constraint on the table.

 

How to do this stuff

 

By using an alter statement.

 

Syntax

 

Alter table "tablename" nocheck constraint "Check_Constraintname"

alter table Student nocheck constraint CK__Student__Marks__29572725
insert into Student(ID,Name,Marks) values(2,'nagaraju',350)

Try to insert the preceding record. When we run the preceding insert query the record is inserted successfully because the 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 the Marks column and see the result.

 

Scenario 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 2 tables. The SchoolName table is just like the Master table. It maintains all the school name Information. The Student table maintains the Schools information. In the Student table the SchoolName column references the ID column in the SchoolName table.

 

Use the following to insert data into the SchoolName table:

insert into SchoolName
select 'HydPublic'
union all
select 'BabyMoon'

Select ScoolName

select * from SchoolName

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

Run the preceding query record to insert the data.

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

Run the preceding query it throws an error because the SchoolName value 3 deos not exist in the ID column of the 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 to Run the preceding Insert Query Successfully

 

Disable the FOREIGN KEY Constraint from the Student table.

 

How to do this stuff

 

Use the alter statement.

 

Syntax

 

Alter table "tablename" nocheck constraint "FOREIGN KEY CONSTRAINT NAME"

alter table Student nocheck constraint FK__Student__SchoolN__4222D4EF

Try to insert the following record.

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

Here the record is insereted successfully .

 

How to enable FOREIGN KEY Constraint

alter table Student check constraint FK__Student__SchoolN__4222D4EF

After enabling, try to insert the following inavlid record and check the result.

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

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

 

Yes

Disable

alter table Student nocheck constraint all

Enable

alter table Student check constraint all

Limitations 

• Enabing and disabling constraints works only to check constraints and foreign key constraints.
• Enabing and disabling constraints do not work for default, primary and unique constraints.