On Thu, 2005-06-09 at 11:45 -0600, Steve Dibb wrote: > I'm working on creating a new table in our database, and I'm wondering > -- why bother with the possibility of NULL values in a field? It seems > like it always adds more work then its worth. > > What's the benefit of adding the option of IS NULL when you usually have > to check to see if its also blank (field = '' OR field IS NULL)? > > Steve
Well, generally NULL-able is the default behavior. To further constrain the data you can make it "NOT NULL" That helps you know more about your data as it forces more structure. Sometimes, this is good. If this can be overcome by entering an empty string then you really don't gain anything. In a DB like PostgreSQL you could use the "rules system" to rewrite that INSERT or UPDATE to make an "" -> "UNKNOWN" or whatever suits your needs. It's a delicate balance. If you force data to be there that is not always available then you are going to get junk data just to satisfy the constraint. Think about how many times you have filled in an EMAIL field on a web page that *required* you to have it with something like "[EMAIL PROTECTED]". If they didn't require it they wouldn't have got "junk in" and then in time got "junk out". The moral is that the integrity of your data is always more important then simplifying the SELECT statements. Both NULL and NOT NULL are useful and have their place. Let the data determine which to go with. One man's opinion... Gabe .===================================. | This has been a P.L.U.G. mailing. | | Don't Fear the Penguin. | | IRC: #utah at irc.freenode.net | `==================================='
