Re: null or not null

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'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. But what is really important about this is that by having a primary key defined as accepting Nulls, 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! 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. 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. One way to watch your back on this with reports is to use Outer Joins when possible. > 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. Too bad there's no equivalent of "Without Null Input" that exists in Perform screens which defaults all of your screen values to space. 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. Cosmo