Re: NULLs in primary keys

akent@cix.compulink.co.uk ("Andy Kent") writes: >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. ....... NULL is neither equal nor not equal to anything, including another NULL. The concept of NULL is that it is a non-existent or unknown value. You cannot test for equality or inequality for a non-existent or unknown value. The SQL works as it should. IMHO, NULL should never be used as the component of an entity identifier. There must always be an existing and known value to identify each instance of an entity. I propose that global cost centers be given an identity and that identity be used as the component of the key in every table where cost center is the primary or foreign key to that table. ___ ___ Senior Consultant / ) __ . __/ /_ ) _ _ __ Informix Software Inc. (303) 850-0210 _/__/ (_(_ (/ / (_(_ _/__) (-' ~/ '(_- 5299 DTC Blvd #740 Englewood CO 80111 dberg@informix.com Opinions expressed herein are my own.