question

MRaj avatar image
MRaj asked

How to Insert Default value of Column In Case of Check Constraint Violation Error

Create table Table1(id int) ALTER TABLE [dbo].[Table1] WITH NOCHECK ADD CONSTRAINT [CK_Table1_ID] CHECK (([ID]>=(0) AND [ID]<=(3))) GO ALTER TABLE [dbo].[Table1] CHECK CONSTRAINT [CK_Table1_ID] Go ALTER TABLE [dbo].[Table1] ADD CONSTRAINT [DF_Table1_Id] DEFAULT ((0)) FOR [ID] Declare @Var int Set @Var=5 Insert into Table1 select @Var I'm getting the below mention error: > Msg 547, Level 16, State 0, Line 7 The INSERT statement conflicted with the CHECK constraint "CK_Table1_ID". The conflict occurred in database "deletedb", table "dbo.Table1", column 'id'. The statement has been terminated. Expected Result :If @Var Values Violates the Check Constraint,Default Constraint Value should be inserted into table,Pls give the suggestion on the same
constraints
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
ThomasRushton avatar image
ThomasRushton answered
That sounds more like you need a [`TRIGGER`][1] as well as a `CHECK CONSTRAINT`. `CONSTRAINT`s are there to enforce integrity, rather than apply changes to enforce integrity. [1]: http://msdn.microsoft.com/en-us/library/ms189799.aspx
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.