Re: NULLs in primary keys

Thanks to everyone for your suggestions (here and on email) on this one. The most common suggestion has been the one in the message quoted below, that is, to re-write the query as something like: > > SELECT things > FROM charges_table > WHERE service_type = p_service_type > AND (cost_centre = p_cost_centre OR cost_centre IS NULL)> The problem I've found with this approach is that only the first part of the composite index on these cols gets used, thereby hurting performance. Consequently the index only whittles down the search to 300 or so rows instead of 1 or 2. RDBMSs just don't like OR clauses do they. You see, my question was actually a considerable over-simplification of a scenario where the primary key has 8 parts of which up to 6 could have NULL values, representing "General". The best solution seems to be to define a value meaning "General", "Global" or "Common" rather than to use nulls at all. Not only does this suit relational design ethics better (because null effectively means "I don't know", whereas in this case we very definitely *do* know), it also saves the engine large numbers of lengthy sequential scans. > Andy Kent (akent@cix.compulink.co.uk) 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) > : ------------------------------------- > > Yeah, this is normal behavior for Informix products. NULL is treated > likeFALSE in an SQL predicate. You could test explicitly for NULL: > > SELECT things > FROM charges_table > WHERE service_type = p_service_type > AND (cost_centre = p_cost_centre OR cost_centre IS NULL)> > I did something very similar once and spent what seemed like days arguing > about why NULL's are good/bad/whatever. There are very strong opinions > out there about NULL. One person insisted that we use a magic value like > "XX" to represent "any" instead of NULL. I couldn't see how that > improvedthe situation. > > It bothers me that Informix treats "blank" differently than NULL. For > onething, you can't see the difference when you PRINT or DISPLAY them but > they have very different effects on a query. If I had my way there > would be no blank character variables other than NULL. > > -- > Jeff Sturm akent@cix.compulink.co.uk (Andy Kent) -------------------------------------