1

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.

Improve this question
9
  • 2
    What value would you expect in COLUMN_DEFAULT if you omitted the DEFAULT value..? Commented Feb 3, 2022 at 17:57
  • @Larnu I apologize. You're right. If you omit the default, is also set to null. Which doesn't explain my point right? In languages like C#, for example, the default expected is "false" in such cases. Commented Feb 3, 2022 at 18:08
  • 1
    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". Commented Feb 3, 2022 at 18:13
  • Yes, but do we agree that the default that the system allows you to set is useless? In case you don't specify the value in this case, you'll see a message that says that the column doesn't accept NULL. So I think is kind of contradictory. What I would expect is the system to allow you only to set either 1 or 0, not other option. Am I wrong? Commented Feb 3, 2022 at 18:17
  • 2
    Yes, you can say bit NOT NULL DEFAULT NULL (example). As explained, the purpose is to ensure that if someone tries to avoid specifying a value for that column, it should fail, rather than specifying some token default value to put in its place. Commented Feb 3, 2022 at 18:22

1 Answer 1

Reset to default
4

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Thanks for the discussion. Nice to learn it!
The example shows exactly what we discussed. Is one of the very few situations where you can't use the DEFAULT keyword like is meant to be.
@MauroBilotti I still don't agree you're interpreting it the right way. The purpose of the syntax is to allow you a way not to set a default value but rather to dictate that when a value is not specified at all (or NULL explicitly) it should violate the constraint instead of silently replacing it with some meaningless token value you decide up front. You could accomplish the same by saying col int NOT NULL DEFAULT -1 CHECK (col <> -1) but that is clumsier and unintuitive IMHO. If only declarative DML had the full scope of all of English.
Yes, I see it. However the main usage that I do probably, is to use it as you said above which is set a default value when you don't want to explicitly specify it on the insert. And that's the possible reason why I see it more like a integrity check rather than a default value. Nevermind, thank you! I accepted it as the answer :)
@MauroBilotti Right, there may of course be situations where setting an acceptable default value is... well... acceptable. But given that there are also occasions where the model requires that the value be specified explicitly, there must be some way to accomplish that as well. If the default value was always a not null value, then we would have to resort to the more verbose and less "semantically correct" type of solution Aaron described.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.