Enable/Disable Constraints in SQL Server

Enable/Disable Constraints in SQL Server 

The best way to insert valid data is with constraints. This 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

1. create table Student  
2. (  
3.   
4.   ID int  primary key,  
5.   Name varchar(20) unique ,  
6.   Marks int not null check (marks>500),  
7.   SchoolName varchar(20) default 'HydPublic'  
8. )  

Scenario 1

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

If we check the folliowing data from the Student table:

1. select * from Student  

Scenario 2

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

Answer:

We need to disable the Check Constraint on the table.

How to do this stuff
Answer:
By using an alter statement.
Syntax
Alter table "tablename" nocheck constraint "Check_Constraintname"
Examaple

1. alter table Student nocheck constraint CK__Student__Marks__29572725
   
   
2. 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

1. select * from Student  

How to enable check constraint on table

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

1. create table SchoolName  
2. (  
3.   ID int identity(1,1) primary key,  
4.   Name varchar(20) unique   
5. )  
6.   
7. Drop table Student  
8.   
9. create table Student  
10. (   
11.   
12.   ID int  primary key,  
13.   Name varchar(20) unique ,  
14.   Marks int not null check (marks>500),  
15.   SchoolName int foreign key references SchoolName(ID)  
16. )  

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:

1. insert into SchoolName  
2. select 'HydPublic'  
3. union all  
4. select 'BabyMoon'  

Select ScoolName

1. select * from SchoolName  

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

Run the preceding query record to insert the data.

1. 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'.

1. 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"

Example

1. alter table Student nocheck constraint FK__Student__SchoolN__4222D4EF  

Try to insert the following record.

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

Here the record is insereted successfully .

How to enable FOREIGN KEY Constraint

1. alter table Student check constraint FK__Student__SchoolN__4222D4EF  

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

1. 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?

Answer:

Yes

Example

Disable

1. alter table Student nocheck constraint all  

Enable

1. alter table Student check constraint all  

Limitations 

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

-- Disable all constraints for database
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
-- Enable all constraints for database
EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"