Re: NULLs in primary keys

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. This is nothing to do with indexes or keys. It is conditional logic on null values that you are incorrectly using. If you have nulls allowed in cost_centre then your query should be written: SELECT things FROM charges_table WHERE service_type = p_service_type AND ( ( cost_centre is null and p_cost_centre is null) or cost_centre = p_cost_centre ) Reason: one NULL value will never equal another NULL value. How can they, because NULL means "unknown". -- +-----------------------------------------------------------------------+ | ## P e a c e Raymond Hayter phone: +64-4-5705739 | | # COMPUTERS Peace Computers fax : +64-4-5705760 | | New Zealand Limited email: raymond@peace.co.nz |