CODEWITHSUNDEEP
sql-servernullschema
Mauro Bilotti
Mauro Bilotti

Reputation: 6242

SQL Server: why is it possible to set default NULL value on NON-NULLable column?

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.

Upvotes: 1

Views: 1589

Answers (1)

allmhuran
allmhuran

Reputation: 4454

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;
}

Edit: From the comments it seems like this argument is a bit abstract, so I'll try to make it clearer using a different example, and a different datatype.

Suppose I have the idea of a "Customer" in my domain. A Customer has a name attribute. In my model (based on my business requirements) it wouldn't make any sense to be able to create a Customer without a name.

In such a scenario, I specify that the name column is therefore not null.

But what should I specify as a default? Clearly it doesn't really make any sense to specify a default value for name. If we set the default to "Bob", we'd really just be lying to ourselves. If we set the default to an empty string we've now shot ourselves in the foot, because all we've done is circumvented the business rule, we haven't actually created any meaningful Customers if those customers have no name.

So in my model I want to specify that in order to create a Customer, you have to supply a legitimate value for name. I therefore make the column not null and make the deliberate choice to not supply an acceptable default.

You could, of course, still explicitly populate the name column with an emptry string when you come along an insert data... but now, that's on you, as the person creating the data. The model has specified what it expects, and you have to be quite deliberate about circumventing that. If we had given the column a meaningless default, people could flout the business rule "accidentally", as it were, without even realising it.

Upvotes: 4

Related Questions