If your column definition does not have default constraint and it is nullable, the nullable field will have null
mark, when you add a row even when you specify DEFAULT
keyword for your insert value. For example for the following table definition
CREATE TABLE Test(
ColA [nvarchar](50) NOT NULL,
COLB [nvarchar](50) NULL)
The first three insert statements will insert NULL mark for ColB. Only the last one will have ColB value assigned.
insert into Test(ColA, ColB) values ('a', DEFAULT);
insert into Test(ColA, ColB) values ('b', NULL);
insert into Test(ColA) values ('c');
insert into Test(ColA, ColB) values ('d', 'e');
Whereas for the following table definition with DEFAULT
constraint.
CREATE TABLE Test(
ColA [nvarchar](50) NOT NULL,
COLB [nvarchar](50) NULL DEFAULT 'x')
Only the second statement below will insert NULL mark for ColB. The first and the third statements will assign 'x' as the DEFAULT value.
insert into Test(ColA, ColB) values ('a', DEFAULT);
insert into Test(ColA, ColB) values ('b', NULL);
insert into Test(ColA) values ('c');
insert into Test(ColA, ColB) values ('d', 'e');
When trying to return the column back to NOT NULL
SQL Server immediately checks the constraint, which fails because the rows for that column were set to NULL
.
For an existing column, you have to update the table to remove any NULL
values before you can set it as NOT NULL
.
Best Answer
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.