RE: NULL vs NOT NULL in database

I dont know if this is mentioned or not, but I kind of like referential integrity. It tends to save one from a lot of difficult problems. More than once I have seen an attempt to not have NULL fields cause misery. If you want to be consistent, you would not be able to have referential integrity on nullable fields. Not using NULLable fields sounds like a lot of work and a lot of pain to me. Then again, I am pretty lazy, I dont like extra work or much pain. Hope this helps, Will >===== Original Message From Scott Black <sblack@elsouth.com> ===== >What do you use when you want to store a single space? > >How do you store a space in a non character field? The database will >treat it as a null anyway (at least on my 7.31 version). > >If you're okay with these, what about losing all of the functionality >that Informix has built in to allow 'unknown' data? Suppose you do >this, and you need to test two columns for equality. If both are null >(space) your test will tell you they are equal, when in fact they are >not. You will now have to add extra code to your joins to filter out >your spaces... Seems like you're right back to square one. > >Instead of having 100 customers without a fax number, now you have 100 >customers with the same fax number! Whoops, have to go back and add >extra code to those join clauses again... > >I can see wanting to do away with the hassle, and potential problems >associated with joining on nulls. I've thought the same thing from >time to time (I imagine most people have), but I would think that >checking every variable for null in every application, and then >replacing with space would be more troublesome then adding an extra >check to your where clauses. > >The more I think about it, the more I begin to appreciate the way >Informix has implemented nulls. After writing this I have gained a new >respect for them. I'm glad you asked the question, and look forward to >reading other's responses. > > > >Just my two cents worth. > > -----Original Message----- >From: Phelps, Mary [mailto:mphelps@safetycenter.navy.mil] >Sent: Friday, July 14, 2000 3:24 PM >Posted To: informix >Conversation: NULL vs NOT NULL in database >Subject: NULL vs NOT NULL in database > > >Is there any advantage/disadvantage to setting all columns to "not null" >and >fill with "space" to avoid problems with table joins? We are >redesigning a >database and have been told to set all columns "not null" and set to >space >when no value exist. Does any one see any gotchas? ------------------------------------------------------------ This e-mail has been sent to you courtesy of OperaMail, as a free service from Opera Software, makers of the award-winning Web Browser, Opera. Visit us at http://www.opera.com/ or our portal at: http://www.myopera.com/ Your free e-mail account is waiting at: http://www.operamail.com/ ------------------------------------------------------------