1

I have a table TempTable with one column having a default constraint

ALTER TABLE [dbo].[TempTable] 
ADD  CONSTRAINT [DF_TempTable_Version]  
DEFAULT ((1)) FOR [Version]

Version is defined as decimal (18,1)

Most of the time the value 1.00 is inserted into the column but in some cases its inserting a null value.

How we can identify if the constraint is failing or if there is some other issues causing this?

Improve this question

2 Answers 2

Reset to default
3

How we can identify if the constraint is failing or if there is some other issues causing this?

The most likely cause is that a client is simply inserting a null, or updating the column to null, neither of which is prevented by a default constraint.

If you don't want nulls, just declare the Version column as NOT NULL, or add a check constraint.

Improve this answer
2

If you give the ALTER TABLE command while there are concurrent insertions, this could be possible, unless you set an high ISOLATION LEVEL. After the command has been issued, every new insertion will provide the correct default value. If null values remain in the column after the initial ALTER TABLE, you can simply change them with an update, e.g.

UPDATE tempTable
SET Version = 1
WHERE Version IS NULL
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.