If the field has DEFAULT should it be NOT NULL?



I was adding a few more columns to a table and fell out of this thought. By default we leave all fields with NULL value, however if it has a DEFAULT it will insert DEFAULT instead of NULL .


If the field has a DEFAULT it should also be set to NOT NULL ? or is it indifferent?

asked by anonymous 20.10.2015 / 17:12

1 answer


They are different things.

The DEFAULT only makes an auto insert if the insert does not have a value for that column, but nothing prevents you from applying a NULL to it after that in another operation, if there is not a NOT NULL clause. Even in INSERT if you explicitly say that you want to set a NULL , DEFAULT will not be considered and the null will be applied if this restriction is not defined.

So if you want to restrict nulls, write this in modeling, do not rely on a transient operation. DEFAULT is only a facilitator, not a restrictor.

Actually I would think if this idea is worth everything NULL by default. I think it should be the other way around. Only when there are clear cases where NULL is useful is that it should be allowed. And it should rarely be useful.

20.10.2015 / 17:19