Re: null or not null

Cosmo Lee <cosmo@echonyc.com> wrote: :Nils Myklebust wrote: :> :> 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. :> : :That depends on what version of the engine you are using. On a version :5 SE engine I've got running here, you can definitely define a field :which is a foreign key to accept Nulls. I didn't mention foreing keys at all. They can be null and appropriately so in some cases. That's another issue. : I've just been grappling with :this issue. Notice I wrote *define*, as in the schema. I haven't :actually tried inserting a Null value into the field, but I imagine the :engine would reject it. It shouldn't unless you say "not null" on the column definition. :But what is really important about this is that by having a primary key :defined as accepting Nulls, Then it's by definition *not* a primary key. : if you define a field in another table as a :Foreign Key Referencing that field, that table will accept Null values :in that field! What that means is that you will have foreign keys :referencing non-existent values in the referenced table! You don't get into this kind of trouble if you understand primary keys. I don't know if you can even define a primary key in ver. 5.x or earlier engines. In 7.x no part of a primary key can be null however you try to do it. Remember in all this we are allways talking about primary (and foreign keys) as defined in the schema. On early engines where primary keys couldn't be defined one had problems and one would have only what could be called logical primary keys or something like that. The only, and appropriate, way to "define" them would be to spesify "not null" on each column and create a unique key on it. Both are required to have a resemblance of a primary key. :I'm sure that this must be a bug, since it runs completely counter to :the whole purpose of Referential Constraints, and could set one up for :some nasty referential integrity surprises. So there is no such bug in the versions I have. :If this is the case with your database engine, then this is a definite :argument *against* allowing Nulls in Primary Key fields. I prefer No :Nulls at all when possible with the default <space> primary key value :entered in each table. Again: A primary key *by definition* disalows nulls. Remember a null means value unknown - a primary key is a *value* that uniquly identifies a row. A null in a primary key is a contradiction. :One way to watch your back on this with reports is to use Outer Joins :when possible. No need for the primary key case. It is appropriate in some other cases. :> 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. : :I never worked with 4GL that much, but it doesn't really seem like :*that* much work. Just write a function to initialize your screen :variables to have space values. Each time you declare a screen-row :variable structure, call the initalization function. *A* function - hundreds of them would be required in my application and in most others of reasonable size. :Too bad there's no equivalent of "Without Null Input" that exists in :Perform screens which defaults all of your screen values to space. I think null is ok in this case. Spaces in the database doesn't make programming or sql much simpler. :Actaully, and even easier way to do this might be to default the values :of your fields to space, at the table level. I haven't tried this mind :you, so you'd have to give it a try to see if when you try to insert a :Null value into a Not Null field, if the engine goes with the default :space value upon seeing the Null insert value. This may work, but I can't realy see *any* reason why one would want to do it on an application wide basis. It would be a lot more work than following the idea underlying 4GL of using nulls. It would also create as significant problems on updates. 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