TSQL - When to use 'not null'

Question

What general guidelines should I go by when considering whether I should mark a field 'not null' as opposed to just declaring everything but the primary key null?

Should 'not null' fields have DEFAULT values?

Answer 1

Depends on how you want your application to behave.

• First, there will never ever ever be a possible row where this value does NOT contain meaningful data, then use NOT NULL. That is, this value will always be meaningful and represent something.
• Do you want the value to always be filled out by the user or programmer in some form or fashion? Use NOT NULL without a DEFAULT
• Do you want it to be optional to users and programmers? Use NOT NULL with a DEFAULT

Answer 2

I think you've got 2 questions there:

Should you mark fields as not null?

Yes, assuming that you never intend a valid row to have a null value in that field. Think of "not null" as the easiest type of constraint you can put on a field. Constraints in a database help ensure the data is kept consistent by meeting expectations.

Should not null fields have defaults?

Only when there is an obvious default. For example the Paid field of an invoices table might have a default of 0 (false). In general it works the other way around - if a field has a default value, then it should probably also be not null.

Don't create defaults just for the sake of defaults - if a field should not be null, but there isn't a universal default, then leave it be. That ensures that any INSERT statements must provide a value for that field.