How to disable constraints of a table ? TIP #74

 

You are reading this post just because of two reason

1) You are curious whether it is possible or not and why we require ?

2) You need to disable constraints  Smile

So , let me share here that you can disable constraints at anytime of a table.

Sometime it is possible when you are doing bulk insert or you need to insert values in column and for this you need to disable constraints.

You can disable all the constraints of a table using following command

ALTER TABLE TABLENAME NOCHECK CONSTRAINT ALL ;

If you want to disable a specific constraint of a table then you can use following syntax

ALTER TABLE TABLENAME NOCHECK CONSTRAINT_NAME

For example suppose you want to disable all the constraints of  student table then you can write following syntax

ALTER TABLE dbo.Students NOCHECK ALL;

Below is very live example in Indian scenario

Suppose you added a check marks in last class more than 45% then only add student now due to some out side pressure you want to give admission to a student who has 40% percent then you need to disable percentage check.

ALTER TABLE dbo.Student NOCHECK chk_Student_Percentage_40

I hope this might help you somewhere.

Thanks

Rj!!!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s