none
Temporarily suspending SQL constraints RRS feed

  • Question

  • Before I make several data changes that will temporarily violate the DataTable's constraints, I set the DataSet's EnforceConstraints to false, and afterward I set it back to true.  That works fine for the DataSet.


    But then when I store the changes to the SQL table, the SQL constraints raise an exception.  How do I tell SQL to temporarily ignore the constraints?


    Is it better to set all the constraints only within the DataSet, and not set any constraints within SQL?

    Saturday, December 15, 2007 9:13 PM

Answers

  • If you have the need to temporarily disable constraints in SQL Server your only choice is a temp table which you can drop after the operation because constraints are implemented for data integrity you have to ALTER the table to Drop constraint and later ALTER the table again to enable constraint.  Hope this helps.

     

    Sunday, December 16, 2007 12:04 AM

All replies

  • If you have the need to temporarily disable constraints in SQL Server your only choice is a temp table which you can drop after the operation because constraints are implemented for data integrity you have to ALTER the table to Drop constraint and later ALTER the table again to enable constraint.  Hope this helps.

     

    Sunday, December 16, 2007 12:04 AM
  • Yes that helps, thanks.  I'm going to enforce data integrity by using constraints within the DataSets only, and not use SQL to enforce constraints except for Identity columns being unique.  I see little practical advantage to enforcing the same constraints both in the DataSets and in the database.  But please tell me if I'm overlooking something.

    Sunday, December 16, 2007 4:43 AM