SQL SERVER| SQL SERVER – Enable or Disable CONSTRAINTs? | SQL Interview Question

In this post we shall understand how we can disable or enable single or all the constraints belongs to a single table with their side effects.

Notes

We cannot disable/enable unique constraints. We can only do drop and recreate unique constraints. We can only do constraint disable for Foreign Key Constraint and CHECK constraints

Lets first go through the syntaxes.

Syntax to DISABLE a single CONSTRAINT.

--

ALTER TABLE TableName 
	NOCHECK CONSTRAINT ConstraintName

--

Syntax to ENABLE a single CONSTRAINT.

--

ALTER TABLE TableName 
	CHECK CONSTRAINT ConstraintName

--

Example of CHECK CONSTRAINT enabling and disabling

Let’s create a table called testConstraints and insert fews rows into it.

--

--Create a new table
CREATE TABLE testConstraints
(
	 Id INT 
	,Val VARCHAR(10)
)
GO

--Insert Some ROWS in the table
INSERT INTO testConstraints VALUES ( 1, 'Pawan' ) , (2, 'Avtaar')
GO

--

Lets add a UNIQUE CONSTRAINT to restrict the Id less than 10.

--

ALTER TABLE testConstraints
ADD CONSTRAINT Ix_Dups CHECK ( Id < 10 )
GO


--

Now lets try to insert data which fails the check constraint.

--

INSERT INTO testConstraints VALUES ( 11 , 'Pawan' ) 
GO


--

OUTPUT

Msg 547, Level 16, State 0, Line 39
The INSERT statement conflicted with the CHECK constraint "Ix_Dups". 
The conflict occurred in database "master", table "dbo.testConstraints", column 'Id'.
The statement has been terminated.


--

Note that now since we have the check CONSTRAINT in place SQL Server does not allows us to insert the above value. Now lets select the data from the table to see if anything got inserted into the table.

--

SELECT * FROM testConstraints
GO

OUTPUT

Id          Val
----------- ----------
1           Pawan
2           Avtaar

(2 rows affected)

--

Clearly nothing got inserted. Now lets disable the unique constraint and see the effects.

--

ALTER TABLE testConstraints 
	NOCHECK CONSTRAINT Ix_Dups
GO
--Commands completed successfully.

--

Now lets try to insert the data again.

--

INSERT INTO testConstraints VALUES ( 11 , 'Pawan' ) 
GO
--(1 row affected)

--

Now let’s select the data from the table to see the data.

--

SELECT * FROM testConstraints
GO

OUTPUT

Id          Val
----------- ----------
1           Pawan
2           Avtaar
11          Pawan

(3 rows affected)


--

We can also enable or disable all the CONSTRAINTs from the table.

Syntax to DISABLE all the CONSTRAINT from a table

--

ALTER TABLE tableName
	NOCHECK CONSTRAINT ALL

--

Syntax to ENABLE all the CONSTRAINT from a table.

--

ALTER TABLE tableName
	CHECK CONSTRAINT ALL

--

Related SQL SERVER Article – For verifying existing and future data.

References

Pawan Khowal

Pawan is a SQL Server Developer. If you need any help in writing code/puzzle or training please email at – pawankkmr”AT”gmail.com. Meanwhile please go throgh the top pages from his blog.

My SQL Groups on Facebook:

1. If you like this post, you may want to join my SQL SERVER Interview Puzzles/Interview Questions on Facebook: https://www.facebook.com/groups/1430882120556342/

2. If you like this post, you may want to join my SQL Server Puzzles on Facebook:
https://www.facebook.com/groups/206594023090781/

My SQL Page on Facebook:

2. For all the updates you may follow my page -> https://www.facebook.com/MSBISkillscom-1602779883299222/

Enjoy !!! Keep Learning

Http://MsbiSkills.com