Re: NULLs in primary keys

On Thu, 15 Sep 1994, Andy Kent wrote: > My customer has a table of services charges, keyed on the type of service > and the cost centre. Some services have different charges for each cost > centre, others are global. At present the global ones have a cost centre > of NULL. > > I have a stored procedure containing a query something like: > > SELECT things > FROM charges_table > WHERE service_type = p_service_type > AND cost_centre = p_cost_centre> > If the row we're after has a NULL cost_centre and therefore p_cost_centre > has been set NULL, the row is never found. The client has OnLine 5.00. > > Now I can understand why nulls in a primary key are generally not a very > nice idea, but this must be a common enough business requirement. > > Questions: does anyone know off the top of their heads whether this would > work in 4GL (which they don't have), ESQL/C or a later version of SPL? > > Has anyone had experience of getting around a similar problem? (I've > tried getting them to use blank instead of null in the interim and this > works but I don't like it) > > Thanks in anticipation. > > akent@cix.compulink.co.uk (Andy Kent) > ------------------------------------- Is there an index on the cost_centre field? I remember we once had a similar problem on a table that used NULL values in the primary key: any select where the key value was NULL would always fail. It arose that this was a known bug (sorry haven't got the number), something about using a column with NULL values as the 2nd or 3rd part of an index. This was using 4.10 RDS and SE 5.00 but would only manifest on the SCO port. It didn't occur on a RS/6000 or a Sun Sparc. Our solution was to remove the field (in your case cost_centre) from the index. This didn't impact on performance as the table wasn't that large (ie not many rows) but that may not be the case with your client. An alternative solution would be to use a recognised value in place of NULL, such as a space (as you've said) or "NONE". This is an ugly workaround and it was easier for us to alter the index. Hope this helps. -------------------------------------------------------------------------- Standard disclaimers apply. | Everything depends. Den Donovan <den@dsl.com> | Nothing is always. Druid Systems Ltd +44 932 571212 | Space is big.