Today I was digging into a issue with difference in schemas between two environments and I realize that was related to the default value set to NULL on a non-Nullable column.

For my surprise, I decided to test it, and as you can see, SQL Server allows you to set NULL on a column that doesn't allow it.

CREATE TABLE TestTable (
    [Id] [uniqueidentifier] NOT NULL,
    [BooleanColumn] BIT NOT NULL DEFAULT NULL 
)   

What I would expect is to see some kind of syntax error.

Why is that possible? Sounds pretty odd.

CodePudding user response：

To reinforce Larnu's comment: By setting a default of null (or simply accepting the system default), you are making it explicit that failing to insert a domain value will cause an error. Suppose you set a default of 1. Then I could insert a row without specifying that column as part of the insert list, or providing any value for the column. That might lead to undesired or unpredicted behaviour. The not null with a null default says "if you want to create a member of this set, this attribute must be defined by you before you can do so"

Fiddle example from Aaron Bertrand

In the comments you also made the comparison with a C# bool, but a C# bool literally cannot be null, which is different from a SQL bit, which can be. In order to make the comparison you would have to compare with a C# bool?, whose default value is null.

C c = new();
if (c.b is null) Console.WriteLine("it is null"); // prints "it is null"

internal class C
{
   public bool? b;
}