Null or Not Null????

[Jim Morris]

I am setting up my db, and am reading a great book on all of this, but am confused on exactly when to use null and not null. The book is explaining it, but I guess I'm just not getting it. Some "plain english" would be GREAT!!!:)

Thanks,
Jim

[bcarl314]

I'm not sure what the "official" guidelines are, but basically, I use NOT NULL for fields in which I need a value.

For example, lets say I've got a table of registered users on a site with the following fields:

UserName
Password
FirstName
LastName

Now, since I'll be using the UserName and Password to validate visitors, I need to make sure both have values, so I set both to NOT NULL, on the other hand, I don't necessarily need to have their First and Last Names, so I can set them to null.

Basically, anything that you NEED should be set to NOT NULL, and everything else can be null.

Hope this helps.

[bcarl314]

Also, one more note. All primary keys must be NOT NULL.

[brothercake]

Originally posted by bcarl314
be NOT NULL

Excellent ... sounds like a Kraftwerk remix of Vanessa Carlton :D

[Jim Morris]

Great!!! Thanks for taking the time to explain that to me. You made it much easier to understand. Once again, thanks!!!:)

Jim

[Kiwi]

You should use Not Null when the consequences of that value missing are serious for the database or the application.

For example, Foreign Keys should usually be "Not Null". As should any references to file information. Basically, the question to ask yourself is, "what use is this record without this piece of information?" If the answer is, "Not much", then the field should be Not Null. Specifically, all fields that describe the data structure should be Not Null. This includes all keys/foreign keys, most date fields, and any other information that you identify a record with.

I generally make most fields not null, set sensible defaults, and then switch off a few that aren't necessary.

The result of not doing this tends to be sloppy data definitions, which tends to make applications less robust.

[Jim Morris]

Thanks Kiwi!!! :)

Jim