Re: NULL vs NOT NULL in database

Your are right Art, serial is the best choice to do that and other cases ... but serial is not perfect, best said, Informix serial is not perfect. I mean: We have been working with informix for 10 years and ours programs (invoincing, accounting, payments, etc) are getting better (of course !). But not all can change at all, you know. Several tables were designed to avoid serial limitation: If you have to export some tables' rows to other boxs, serial doesn't work since "only" have 4 bytes and if you want to get those rows, you should assing some range to that serial field for every database implied in order to avoid duplicates: 0 for 1rt, 0100000000 for 2nd, 0200000000 for 3nt and so on ... So, very nuisance. You could say: that's enough ! but that is not the case ... Nowadays, that tends to avoid it since communications are better and cheaper, but serial limitation remains ... I think SQL-SERVER, maybe ORACLE, does have a good "universal" serial type. I don't know what it is like ... Manel Falcó On Wed, 19 Jul 2000 09:33:09 -0400, "Art S. Kagel" <kagel@bloomberg.net> wrote: > >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 >

Hi Manel, One solution to that particular limitation on SERIAL and SERIAL8 is to include a one or two byte field in addition to the serial column that encodes the source server so that serial number clashes cannot occur. This also does not significantly add to the key overhead while more 'intelligent' keys do. I have struggled with this one for years, and you are correct it is annoying and I have often thrown up hands, as you apparently have, and used intelligent keys for primary and foreign keys. I am ALWAYS sorry because there is always a problem down the road, usually the unavoidable need for a new table with 8 bytes of attribute but 24 bytes of key. Art S. Kagel "Manel Falcó i Aige" wrote: > > Your are right Art, serial is the best choice to do that and other > cases ... but serial is not perfect, best said, Informix serial is > not perfect. I mean: > We have been working with informix for 10 years and ours programs > (invoincing, accounting, payments, etc) are getting better (of course > !). But not all can change at all, you know. > Several tables were designed to avoid serial limitation: If you have > to export some tables' rows to other boxs, serial doesn't work since > "only" have 4 bytes and if you want to get those rows, you should > assing some range to that serial field for every database implied in > order to avoid duplicates: > 0 for 1rt, 0100000000 for 2nd, 0200000000 for 3nt and so on ... So, > very nuisance. > You could say: that's enough ! but that is not the case ... > > Nowadays, that tends to avoid it since communications are better and > cheaper, but serial limitation remains ... I think SQL-SERVER, maybe > ORACLE, does have a good "universal" serial type. I don't know what it > is like ... > > Manel Falcó > > On Wed, 19 Jul 2000 09:33:09 -0400, "Art S. Kagel" > <kagel@bloomberg.net> wrote: > > > > >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 > >