CODEWITHSUNDEEP
sqlsql-server
user72491
user72491

Reputation: 3675

TSQL - When to use 'not null'

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?

Upvotes: 7

Views: 10969

Answers (5)

HLGEM
HLGEM

Reputation: 96542

Not null fields need to have defaults if they are appropriate especially if you are adding a new field to the table (or changing the field from allowing nulls to not allowing nulls) and you need to give a value to all existing records. However a default is not appropriate or available for all possible fields. For instance, we have a person table, lastname is not allowed to be null. There is no default lastname we could assign though, if the person doesn't have a name, the record doesn't get created. On the other hand, you might have a DateCreated field with a default value of the current date. This is also a field that you would want to have as not null and you would want to make sure that the current date was put in whether the record was inserted from the user interface or from an import or from the query window.

Upvotes: 1

David
David

Reputation: 25450

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.

Upvotes: 4

cdonner
cdonner

Reputation: 37648

Do what is semantically correct.

  • NULL - does not always exist
  • NOT NULL - always exists

Try not to define and persist artificial values, like "No value selected" for a drop-down field, or a "No Manager" for an employee's manager.

Whether to use defaults depends on how data gets inserted. If there is a UI with validation, you don't need defaults, IMHO.

Upvotes: 1

hova
hova

Reputation: 2841

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

Upvotes: 13

TheTXI
TheTXI

Reputation: 37875

A lot of people look down upon so-called "magic numbers" and would advocate leaving the field as null instead of putting a default down. The only time I ever use default values is when I have a bit field and I just want it to default to false.

Upvotes: 1

Related Questions