The best way to insert valid data in database is with the constraints. It is also known as data enhancing,data enforcing or data integrity. We already know that SQL Server has different types of constraints. They are
- Primary key constraint
- Default key Constraint
- Check Constraint
- Foreign key Constraint
- Unique key Constraint
Going to Discuss How to Enable/Disable Constraints in SQL Server:-
I am going to explain these points below with a small demo
- Create on table with constraints
create table tblStudent ( Student_Id int primary key, Sname varchar(20) unique , Marks int not null check (marks>500), School varchar(100) default 'Thomas Jefferson High School' )
- insert one record in table
insert into tblStudent(Student_Id,Sname,Marks) values(1,'Adam',600)
- check the data in a table
select * from tblStudent
- try to Insert second record
insert into tblStudent(Student_Id,Sname,Marks) values(2,'susan',400)
If we are trying to insert a second record with the marks of a value of 400 then when we run the above insert query it gives an error because:
The INSERT statement conflicted with the CHECK constraint "CK__tblStuden__Marks__5832119F". The conflict occurred in database "ABCDEMO", table "tblStudentt", column 'Marks'.
- question is How to insert 400 marks into marks column? For this query We need to disable the Check Constraint on the table
Alter table "tablename" nocheck constraint "Check_Constraintname"
Alter table tblStudent nocheck constraint CK__tblStuden__Marks__5832119F
- After disable the constraint we Try to insert the preceding record. When we run the preceding insert query the record is inserted successfully because the check constraint is disabled at this time.
insert into tblStudent(Student_Id,Sname,Marks) values(2,'susan',400) select * from tblStudent
- How to Enable check constraint on table
Alter table tblStudent check constraint CK__tblStuden__Marks__5832119F
Find below queries When we want to Enable and Disable All Constraint for Table and Database
- Enable all table constraints in a database
ALTER TABLE Your_Table_Name CHECK CONSTRAINT ALL
- Disable all table constraints in a database
ALTER TABLE Your_Table_Name NOCHECK CONSTRAINT ALL
- Disable single constraint in a Table
ALTER TABLE Your_Table_Name NOCHECK CONSTRAINT Your_Constraint_name
- Enable single constraint in a Table
ALTER TABLE Your_Table_Name CHECK CONSTRAINT Your_Constraint_name
- Disable all constraints for database in SQL Server
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
- Enable all constraints for database in SQL Server
EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"