3

When I right click on a default constraint and I ask SQL Server to create a CREATE script for it, it generates the following code: 

ALTER TABLE [dbo].[tblEventTurnJudgeStartValues]  WITH NOCHECK ADD  CONSTRAINT [tblEventTurnJudgeStartValues_ExecutionToggle] CHECK  (([ExecutionToggle]=(1) OR [ExecutionToggle]=(0) OR [ExecutionToggle]=(-1)))
GO
ALTER TABLE [dbo].[tblEventTurnJudgeStartValues] CHECK CONSTRAINT [tblEventTurnJudgeStartValues_ExecutionToggle]

For the record, I understand the first ALTER statement but I do not understand what the the second alter statement does. Tried to google the "CHECK CONSTRAINT" phrase but only got hits on the add constraint syntax.

Thanks.

Seth

update
Thanks Joe for your answer. Found this link which helps.

http://blog.sqlauthority.com/2009/11/12/sql-server-disable-check-constraint-enable-check-constraint/

I did not know that you could enable and disable constraints. Cool!

Seth

Seth Spearman
  • 6,710
  • 16
  • 60
  • 105

1 Answers1

3

The first statement creates the constraint, but since it is created with NOCHECK, existing data is not validated at the time of creation.

The second statement simply turns the constraint on and is technically redundant.

Personally, I'd prefer the second statement be written with the WITH CHECK option, which will validate all existing data against the constraint and will prevent the constraint from becoming untrusted.

ALTER TABLE [dbo].[tblEventTurnJudgeStartValues] WITH CHECK CHECK CONSTRAINT [tblEventTurnJudgeStartValues_ExecutionToggle]
Community
  • 1
  • 1
Joe Stefanelli
  • 132,803
  • 19
  • 237
  • 235
  • Joe, I thought that is what it might do. What if data does not pass the constraint test. What happens then? Does the first alter statement fail? – Seth Spearman Nov 15 '10 at 20:24
  • +1: I've always used the `WITH CHECK` to enable a disabled constraint – OMG Ponies Nov 15 '10 at 20:27
  • Joe, Are you saying that without the second statement the constraint is created but is not enforced until the second alter statement? And if you use the WITH CHECK and not all records pass the test the constraint remains disabled? – Seth Spearman Nov 15 '10 at 20:34
  • @Seth Spearman: If the data does not pass the constraint test, you'll see an error like `The ALTER TABLE statement conflicted with the CHECK constraint "tblEventTurnJudgeStartValues_ExecutionToggle".` – Joe Stefanelli Nov 15 '10 at 20:35
  • One other question. If you ONLY use the first statement but use WITH CHECK does that mean that the check is not created if the check test fails? – Seth Spearman Nov 15 '10 at 20:35
  • @Seth Spearman: The constraint is active with the first create statement. The second statement is technically redundant. If you used the `WITH CHECK` on the first statement and existing data failed to satisfy the constraint, the constraint would not be created. – Joe Stefanelli Nov 15 '10 at 20:39
  • Thanks Joe...you have answered all my questions. You rock. – Seth Spearman Nov 15 '10 at 20:39