Re: null or not null

volverine <volverine@mindspring.com> wrote: :Ok guys, I need your help on this. :I recently had a "fight" with my supervisors ( who never worked as DBA ) :for "null" and "not null". :According to them, they want to change all the "null" columns in :database to "not null" columns and put a "default" on them. :All they want me to prove is, why can't they do it ?? :Space is not a problem here. But will the performance will get :affected by this ? I really need to have some points to fight them :and surprisingly I am running out of them. Please help !!! :Thanx in advance. Some columns should be defined with not null, some should not have it. All columns that are part of a primary key must allways be not null, but if you spesify the primary key (as you should allways do) it's handled without you having to put in the not null for each column. If you write applications in 4GL you will have a lot of extra work to do (realy *a lot*) if you spesify every column that the user may leave blank on the screen as not null in the database. The programmers will then have to set the corresponding variable to a space before data can be inserted. This would be plain crazy. The issues are different in other languages. However the 4GL idea of defaulting screen fields where no data is input to null is generally a sound one. There is hardly any way you can design a user interface so the end users can see the difference between a column containing null and one containing spaces when displayed. (Would you use different colors?). Also there is usually no sense in doing that. The reason why they want to use default values instead of null may be that they have read C.J.Date's articles on this. If this is the case they have however missed Dates point entierly. He has some arguments one should not use null. However his alternative with using default values also requires extensive support for this in the database (or rather in the relational language used to access it) as he have explained. Non of the major commersial databases have this support (I don't know of any, even non major, that have it), so there is no way one can currently use it. If you do not have things like "is default" in the language and probably also proper domains (not the stupid SQL "domains") it's not possible to use default values in the way Date wants to do it. SQL is built with the assumption that null is used for some purposes. You either live with it or do something other than designing relational databases with SQL. Nils.Myklebust@idg.no NM Data AS, P.O.Box 9090 Gronland, N-0133 Oslo, Norway My opinions are those of my company The Informix FAQ is at http://www.iiug.org