Hands-On SQL Server Training
Labs • Portfolio • Interview Prep • Career Support
SQL DBA School mobile navigation
SQL Career School
Apply Now
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:
- Primary key constraint
- Default key Constraint
- Check Constraint
- Foreign key Constraint
- 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?
YesDisable
- 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.