Re: NULL vs NOT NULL in database
--- manel@semic.es (Manel Falc' i Aige) > wrote: >A case could be a field that users can fill or not on an input. >This field wouldn't be a primary key but it could be part of a >composite index. Why not ? life is not so simple ... > >Manel I guess I would have to have a more comprehensive description of what the field is used for, why it's important enough to be indexed on and yet not important enough to require input. >On Mon, 17 Jul 2000 13:18:38 -0700 (PDT), Carlos Benjamin ><benj@firstlinux.net> wrote: > >> >> >> >>--- manel@semic.es (Manel Falc' i Aige) >>> wrote: >>>>The main problem is detecting the difference between a "REAL" blank >>>>value and one that indicates a NULL. Do you care? Even if you do not >>>>today, you may in a month, things like this are inevitable. >>>Murphy's law ? >>> >>>But I have an example that it seems better to put spaces instead of >>>null: >> >>But why even ask if your example is what you've already settled on? >> >>>If this field is a key (usually of a composite key), null value can >>>be a problem in order to make some joins ... >> >>Define 'problem'. >> >>Why is it a key field if it can contain nulls? That sounds like more of a potential problem to me. >> >>>in the other hand, you have to convert explicitly the null value to >>>spaces before recording, and that is so nasty and annoying ... >>> >>>Regards, >>>Manel Falc', >>> == Maintainer of the procrastinator's FAQ. Well, maybe tomorrow I will be. _____________________________________________________________ Want a new web-based email account ? ---> http://www.firstlinux.net
Imagin you have two kinds of customers that you want to invoice. The first one doesn't have any branch or department (or maybe sometimes) and the second one always has some branches or department. Therefore, the bills file could have costumer+branch as composite primary key. Branch key could be sometimes null (o space) because not always is required, but it's a key since you could have some files related to, for exemple discounts, payment files for each customer or customer+branches. Here is the join problem. Branch must be blank. So, As input statements don't accept blank value ... You'll have to put it afterwards... and it's a nuisance ... Well, despite of my English I hope you have understood it ... Regards from Lleida, a little corner from Catalonia, Spain. Manel Falcó >>> >>>Define 'problem'. >>> >>>Why is it a key field if it can contain nulls? That sounds like more of a potential problem to me. >>> >>>>in the other hand, you have to convert explicitly the null value to >>>>spaces before recording, and that is so nasty and annoying ... >>>> > >== >Maintainer of the procrastinator's FAQ. Well, maybe tomorrow I will be. > >_____________________________________________________________ >Want a new web-based email account ? ---> http://www.firstlinux.net
"Manel Falcó i Aige" wrote: > > Imagin you have two kinds of customers that you want to invoice. The > first one doesn't have any branch or department (or maybe sometimes) > and the second one always has some branches or department. > > Therefore, the bills file could have costumer+branch as composite > primary key. Branch key could be sometimes null (o space) because not > always is required, but it's a key since you could have some files > related to, for exemple discounts, payment files for each customer or > customer+branches. Here is the join problem. Branch must be blank. Join to what? Detail records? Then there is your problem. Make the customer+branch a secondary key, leave the branch NULL if there are not branches (there WILL be a customer out there who uses branches EXCEPT for the main office which is branch " "). Now alter the table to contain a serial column, populate it, use the serial column for primary key and the foreign key in the detail table(s). NOW you no longer have a problem trying to join NULLs AND your join key is now 4 bytes instead of 40 or more. If you do not have a natural primary key, and often even if you do, SERIAL is the best choice for an artificial primary key. Art S. Kagel > So, As input statements don't accept blank value ... You'll have to > put it afterwards... and it's a nuisance ... > > Well, despite of my English I hope you have understood it ... > > Regards from Lleida, a little corner from Catalonia, Spain. > Manel Falcó > > >>> > >>>Define 'problem'. > >>> > >>>Why is it a key field if it can contain nulls? That sounds like more of a potential problem to me. > >>> > >>>>in the other hand, you have to convert explicitly the null value to > >>>>spaces before recording, and that is so nasty and annoying ... > >>>> > > > >== > >Maintainer of the procrastinator's FAQ. Well, maybe tomorrow I will be. > > > >_____________________________________________________________ > >Want a new web-based email account ? ---> http://www.firstlinux.net