Is it good practice to set all database columns as NOT NULL ?

Question

Normally is it good practice to set all database columns as NOT NULL or not ? Justify your answer.

Answer 1

No. It's a good idea to set columns to NULL where appropriate.

Answer 2

I kind of disagree with the "where appropriate" rule. It is actually rather safe to set any column to be NOT NULL; and then later modify the columns to allow NULL values when you need them. On the other hand, if you allow NULL values first and then later decide you don't want to allow them, it can potentially be much more difficult to do this.

It may make your database table/column descriptions quite ugly if you do this excessively, but when in doubt, go ahead and restrict the data.

Answer 3

Short answer: it depends on what you are storing.

I can see a table (or two) having all NOT NULLS or all NULLS. But an entire database?

Answer 4

Only for columns where not having a value doesn't make any sense.

Nulls can be very handy; for one thing, they compress beautifully. They can be a nasty surprise when you don't expect them, though, so if you can't have a Student without a First Name -- make that column NOT NULL. (Middle names, on the other hand... maybe you want to have a default empty string, maybe not -- decent arguments both ways)

Answer 5

You should not forget to set not null where needed, use check constraints if applicable, not forget about unique constraints, create proper indexes and brush your teeth after every meal and before going to bed:)

In most cases you can use not null and you should use not null. It is easier to change not null->null than in opposite direction, but for example in Oracle empty string is treated as null, so it is obvious that you can't use it all the time.

Answer 6

The inventor of the NULL reference (1965) recently called it his "billion-dollar mistake": http://qconlondon.com/london-2009/presentation/Null+References%3A+The+Billion+Dollar+Mistake

Languages such as Scala, SML, and Haskell are non-NULL by default: NULL is called "Option" or "Maybe" and require special syntax and checks.

Since the time databases were invented, allowing NULL by default has been considered more and more dangerous and undesirable. Should databases follow? Probably.

Go with NOT NULL when you can.

Answer 7

IMO, using NULLable option must be minimized. The application should designate a suitable value for the "non-existent" state. In Peoplesoft I think, the application puts a 0 for Numericals and a space for Char columns where a value does not exist.

One could argue why the so-called suitable value couldn't be NULL.

Because SQL implementation treats nulls totally differently.

For e.g. 1 = NULL and 0 = NULL both result in false! NULL = NULL is false! NULL value in GROUP BY and other aggregate functions also create unexpected results.

Answer 8

From my perspective, while it may be better for the database, it's not better for the user. Once you get into more interactive applications, you want to be able to persist the data in an interim state, so most of your fields will probably be null at that point.

Answer 9

Relational theory has it that NULL is evil.

However, your question kind of referred to practice.

So, to the extent that you want your practices to conform to the heavenly ideals of theory, yes, avoid NULL as if it were the plague and the cholera and aids all-in-one.

To the extent that these crappy implementations called "SQL dbms" do not leave you any other choice, yes, (snif) use them.

EDIT

Someone mentioned "business rules" as the guideline for "appropiateness" in the accpeted answer, and some others upvoted that remark. That is total crap. Business rules can always do without nulls and the only guideline to "appropriateness" is the very deficiencies of any SQL system that makes it a non-relational system to boot.

Answer 10

If you can't know the value at insert time, you really must have a null allowed. For instance, suppose you havea record that includes two fields, begin date and end date. You know begin date when the record is inserted but not the end date. Creating a fake date to put in this field just to avoid nulls is dumb to say the least.

In real life at least as much harm is caused by forcing data entry into a field as by not forcing it. If you havea an email field and don't know the customer's email, then the user has to make something up to put into the required field. Likely what they make up may not be what you would want them to make up something like "[email protected]". Sometimes this bad info gets provided back to the client or to a vendor in a data feed and your company looks really really stupid. I know as I process a lot of these feeds coming in from our customers. Nice things in the email field have included, "his secretary is the fat blonde", "this guy is a jerk" etc.

Answer 11

In business apps I was always removing my NOT NULLS because the users did not like being forced to enter data that they didn't know. It depends on the table but I set most of my fields to NULL and only set the bare minimum number of fields to NOT NULL.

Answer 12

If your data can actually BE "unknown", and it's important to record that fact, then yes, use a NULL. Bear in mind that sometimes you need to differentiate between "unknown" and "not relevant" - for example, a DateTime field in one of my databases can either be the SQL Server minimum date (not applicable), NULL (unknown), or any other date (known value).

For fields which don't really have business rules depending on them - I'm talking about "Comments", "Description", "Notes" columns here - then I set them to default to empty strings, as (a) it saves dealing with nulls, and (b) they are never "unknown" - they just aren't filled in, which logically is a known empty value.

E.g.:

CREATE TABLE Computer (
  Id INT IDENTITY PRIMARY KEY
  , Name NVARCHAR(16) NOT NULL
  , ...[other fields]...
  , Comments NVARCHAR(255) NOT NULL
      CONSTRAINT DF_Computer_Comments DEFAULT (N'')
)

If you don't supply a value to Comments, it defaults to empty.