Default constraint issue

Question

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?

Answer 1

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.

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