Disable and Enable Constraints in a Table in SQL Server

Question

Post reply

Disable and Enable Constraints in a Table in SQL Server

Mr. Kapsicum

SSCertifiable

Points: 6128
More actions
August 14, 2014 at 12:46 am

#298099

Hi All,
I have a Table, on which i am performing Bulk Insert Operations. For This,
i am First Disabling Constraints on that table and After Insert Operation , i am enabling constraints.
My Question is , When Constraints are enabled again, is it going to validate the constraints to check if any violation of constraints happened in bulk Insert Operation.
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Mr. Kapsicum SSCertifiable Points: 6128 More actions · Answer 1

Mr. Kapsicum

SSCertifiable

Points: 6128

August 14, 2014 at 1:12 am

#1737925

Anyone with the Answer.?

BrainDonor SSCoach Points: 19254 More actions · Answer 2

You need to be patient when asking for assistance - a lot of people here have full-time jobs and answer when they are able.

Have a look at WITH NOCHECK, which applies a constraint to new data only - not always a good idea.

http://technet.microsoft.com/en-us/library/ms179491(v=sql.105).aspx

Disabling constraints during a bulk insert can improve performance but make sure you put the extra work in beforehand to ensure the data is valid. Then when you re-apply the constraints you won't have a problem and by not using WITH NOCHECK you know that all of the data satisfies the constraint.

Steve Hall
Linkedin
Blog Site

Perry Whittle SSC Guru Points: 234013 More actions · Answer 3

Mr. Kapsicum (8/14/2014)
Anyone with the Answer.?

Patience is a virtue my friend, as well as an opera 😉

This very same topic is covered here

To explain briefly

The WITH CHECK or NOCHECK applies to existing data and either validates it or ignores it.

The CHECK or NOCHECK CONSTRAINT applies to new data coming in and will either validate it or ignore it.

Hence, the following will enable the checking of new data and not existing

ALTER TABLE [schema].[tablename] with NOCHECK CHECK CONSTRAINT [myFKconstraint]

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Mr. Kapsicum SSCertifiable Points: 6128 More actions · Answer 4

BrainDonor (8/14/2014)
You need to be patient when asking for assistance - a lot of people here have full-time jobs and answer when they are able.
Have a look at WITH NOCHECK, which applies a constraint to new data only - not always a good idea.
http://technet.microsoft.com/en-us/library/ms179491(v=sql.105).aspx
Disabling constraints during a bulk insert can improve performance but make sure you put the extra work in beforehand to ensure the data is valid. Then when you re-apply the constraints you won't have a problem and by not using WITH NOCHECK you know that all of the data satisfies the constraint.

Thanks for the Reply BrainDonor,

I Understand People are Full time working, but it was important for me that time to have some answer.

I am a regular user of this forum, and have been posting my doubts and query here, and understands that sometimes it takes time to have your answer.

Thank you very much for your reply, it was a great Help. Thanks 🙂

Mr. Kapsicum SSCertifiable Points: 6128 More actions · Answer 5

Perry Whittle (8/14/2014)
Mr. Kapsicum (8/14/2014)
Anyone with the Answer.?
Patience is a virtue my friend, as well as an opera 😉
This very same topic is covered here
To explain briefly
The WITH CHECK or NOCHECK applies to existing data and either validates it or ignores it.
The CHECK or NOCHECK CONSTRAINT applies to new data coming in and will either validate it or ignore it.
Hence, the following will enable the checking of new data and not existing
ALTER TABLE [schema].[tablename] with NOCHECK CHECK CONSTRAINT [myFKconstraint]

Thanks Perry, for your reply. 🙂

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 6

Mr. Kapsicum (8/14/2014)
Hi All,
I have a Table, on which i am performing Bulk Insert Operations. For This,
i am First Disabling Constraints on that table and After Insert Operation , i am enabling constraints.
My Question is , When Constraints are enabled again, is it going to validate the constraints to check if any violation of constraints happened in bulk Insert Operation.
Thanks

To be honest, you should never BULK INSERT directly into the final table. There's just too much that can go wrong. For example, while you have the constraints disabled, someone else could sneak a row in that wouldn't pass the constraint test in the future. Always use a staging table and a proper "upsert". It takes a little extra time but it's definitely worth it.

I share the "be patient" sentiment the others have expressed. It takes a little time to get an answer on this site but it's usually worth the wait because most folks really care about the quality of their answers and not the points.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)