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?

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, privacy policy and cookie policy

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