Geek Answers Handbook

TSQL - When to Use 'not null'

What general recommendations should I consider when considering whether to mark the non-null field and not just declare everything but the null primary key?

Should non-zero fields be DEFAULT?

+4
source share
5 answers

Depends on how you want your application to work.

  • First, there will never be an ever possible row where this value does NOT contain meaningful data, and then use NOT NULL. That is, this value will always make sense and represent something.
  • Do you want the value to be always populated by the user or programmer in one form or another? Use NOT NULL without DEFAULT
  • Do you want this to be optional for users and programmers? Use NOT NULL with DEFAULT
+7
source

I think you have 2 questions:

Should you mark fields as non-empty?

Yes, assuming you never intend for a valid string to have a null value in this field. Think that non-null is the simplest type of constraint you can put in a field. Database restrictions help ensure data consistency while meeting expectations.

Shouldn't the default values ​​for null fields?

Only if there is an obvious default value. For example, in the Paid field of the invoice table, the default value may be 0 (false). In the general case, this works the other way around: if the field has a default value, then it should also be non-zero.

Do not create default values ​​only for default values ​​- if the field should not be null, but there is no universal default value, then leave it. This ensures that any INSERT statements must provide a value for this field.

+3
source

Many people look at the so-called "magic numbers" and will advocate to exclude the field as zero instead of rejecting by default. The only time I use the defaults is when I have a bit field and I just want it to be false by default.

+1
source

Do what is semantically correct.

  • NULL - Doesn't Always Exist
  • NOT NULL - always exists

Try not to define and save artificial values, such as "No value selected" for the drop-down field or "No manager" for the employee manager.

Whether or not to use the default values ​​depends on how the data is inserted. If there is a validated user interface, you do not need the default values, IMHO.

0
source

Non-empty fields should have default values ​​if they are appropriate, especially if you add a new field to the table (or change the field to allow nulls to not allow zeros), and you need to give a value to all existing records. However, the default value is not suitable or available for all possible fields. For example, we have a person table, lastname cannot be null. There is no last default name that we could assign, if a person does not have a name, no record is created. Alternatively, you might have a DateCreated field with a default value of the current date. This is also a field that you would like to have as non-zero, and you would like to make sure that the current date was placed in whether the record was inserted from the user interface or from the import or from the query window.

0
source

More articles:


All Articles