UNIQUE constraint on non-"key" columns

From: <spamtrap-at-janhv_at_comhem.se>
Date: Fri, 26 Nov 2004 04:54:44 GMT
Message-ID: <8gbdq0ppdg16gvp9ra2ch8at9qve1g38u6_at_4ax.com>



Hello.

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?

-- 
Jan Hvarfvenius
NB: The e-mail address is not invalid
Received on Fri Nov 26 2004 - 05:54:44 CET

Original text of this message