0

I'm looking over an old employees SQL code and understand it. Why would they set a new column defaulting to 0 but making it null. Or in this case why even bother placing NULL. If you don't add it won't it already accept NULL?

CREATE TABLE Inventory(

Amount INT DEFAULT ((0)) NULL,   --why not just write DEFAULT(0) with no null?
Type   INT DEFAULT ((0)) NOT NULL
)
larug
  • 29
  • 4
  • Explicit is better than implicit: https://stackoverflow.com/a/33219284/5070879 – Lukasz Szozda Apr 10 '19 at 22:41
  • This is off-topic since it is primarily an opinion-based question. Does it make sense? Depends on your perspective. And the evolution of a schema over time often is a factor. As posted, your table makes little sense IMO - with or without defaults. – SMor Apr 11 '19 at 01:24

1 Answers1

2

Normally, when there is a default of 0, the column is NOT NULL.

But, this allows the column to be set to NULL later in the processing. There may be some cases where this is useful. For instance, if the row represents an item and the column is the count of items involved in something. Then 0 might mean that there are no items. And NULL might mean that the item is (softly) deleted.

The above is just for a thought exercise. Personally, I would have a separate flag for soft deletion.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786