RE: NULL vs NOT NULL in database

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?