Re: NULLs in primary keys

Walt Hultgren {rmy} (walt@mathcs.emory.edu) wrote: : In article <35c2ds$2pv@emory.mathcs.emory.edu> den@dsl.com (Den Donovan) : writes: : >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. : Hmm.... I'd argue just the opposite. The connotative value of NULL is : closer to "don't know" than it is to "none," even though it works out in : some applications that the two are functionally equivalent. : If a real-world data item is important enough to be a key column in the : database, I would try to choose a design that assigns it a value in every : situation. If the cost center is global or corporate-wide, I would use : a value to denote that. If that situation happens often, you could set : your forms to default to that value. Generally I'd agree, but NULL has one useful property: it always comes first in an ascending sort. I once built an application that included a job rate table. Here is a simplified version: create table jobrates ( job_code char(6), location char(6), pay_rate decimal(12,2) ); The job_code was required but there had to be a special value for location which meant "any". If a specific location was specified for a job, it's rate overrode the default. Running the following select statement always gave us the desired rate first: select pay_rate from jobrates where job_code = ? and (location = ? or location is null) order by location desc (Our actual problem involved many more columns, most with possible default values. The column order in the ORDER BY clause defined the precedence of defaults. This turned out to be the most workable solution.) -Jeff