Re: UNIQUE constraint on non-"key" columns

spamtrap-at-janhv_at_comhem.se wrote:

>After waking up at 04:30 and not being able to go to sleep again, a
>thought crossed my mind, regarding a table of inventory items. You
>have a table like this:
>
>CREATE TABLE items
>(
> item_no VARCHAR(15) NOT NULL,
> item_descr VARCHAR(40) NOT NULL
> UNIQUE (item_no)
>)
>
>My thought was that item_descr rather should be UNIQUE as well,
>because how would you otherwise tell one item from another, except for
>the item_no? This implies that item_no is nothing else than a
>shorthand for item_descr.
>
>An example.
>
>Suppose you are into selling clothes, and you have the following
>stock:
>
>item_no item_descr
>------- ----------
>shrt1B Shirt, black
>shrt1BHQ Shirt, black
>
>(These are unisize and unisex models)
>
>They differ only in price, because of different manufacturer. Would
>you add something like "High Quality" to the description of the more
>expensive (and therefore of higher quality) shirt, or would you make
>that out of the item_no? Suppose you use an EAN or the like? In that
>case, you will not be able to tell anything about the shirt from the
>item_no, so a black shirt is like any other black shirt, given the
>item_descr above.
>
>So, is it only fatigue or are there any valid points in this
>reasoning?

     Maybe your business rules require it. I can see its possible utility, but in general, I would not want to be limited like that.

     What might be more useful is a DBMS-generated description that includes the relevant factors. The actual description could still be used.

Sincerely,

Gene Wirchenko

Computerese Irregular Verb Conjugation:

     I have preferences.
     You have biases.
     He/She has prejudices.