Re: Primary key, Foreign key and indexes in Informix

----- Original Message ----- From: "Andrew Hamm" <ahamm@mail.com> To: <informix-list@iiug.org> Sent: Wednesday, April 28, 2004 3:42 AM Subject: Re: Primary key, Foreign key and indexes in Informix [On indices on potential FK columns with poor data distribution] > If you do > adequate testing in development then any software faults should be caught > before it ever gets to production. Heh, the famous last words :-) I am still in favour of some kind of db-level consistency checking - be it using referential constraint, check constraint, trigger.... No such thing as perfectly tested application. BTW, good old Informix 5 was terrible with this kind of indices - not only when inserting or updating; since meaningful distribution statistics were missing, the optimizer very often got it completely wrong. Bonzi sending to informix-list

Dragi Raos wrote: > >> If you do >> adequate testing in development then any software faults should be >> caught before it ever gets to production. > > Heh, the famous last words :-) yup - very famous. An alternative which would be just as rigorous would be to use a trigger on the production database. Set the trigger to fire on insert or update of the field, and it can do a lookup into the little reference table. That way you get the strong validation without having to suffer the cost of the FK index. > I am still in favour of some kind of db-level consistency checking - > be it using referential constraint, check constraint, trigger.... No > such thing as perfectly tested application. No, but when something can so severely compromise performance you need to shift the compromise. Perhaps the paranoid could run a regular process looking for bad values that have crept in to a column that has a removed FK. However then you'd only know that there is a problem, but not which program is responsible. > BTW, good old Informix 5 was terrible with this kind of indices - not > only when inserting or updating; since meaningful distribution > statistics were missing, the optimizer very often got it completely > wrong. yeah true. the 7's and 9's seem to get less confused with crap indexes.