Re: Two-field unique constraint

Kalle W <kawi9365NOkaSPAM_at_student.uu.se.invalid> wrote:
>I wonder if there is a way to make a constraint on a table so
>that the combination of two fields has to be unique but not
>forcing each field by itself to be unique?
>
>example:
>
>Ive got a table WORDS with field WORD_ID,CATEGORY_ID and
>WORDTEXT. WORD_ID is a primary key. CATEGORY_ID is a foreign key
>and WORD_TEXT is just the text of the word. I wonder if there is
>a way to make a contraint so that there cant be two(or more)
>WORD_ID's with the same CATEGORY_ID and WORD_TEXT.
>
>/Kalle
>

If you declare a unique constraint on col2 and col3 where col1 is the PK then it would be impossible for the same conbination of col2 and col3 values to be associated with more than one col1 value. However it may not be practical to declare such a constraint as in your example since col3 is a text field and may be either too large to index or inefficient to index.

The fact that the same combination of col2 and col3 values could appear in a three column table where col1 is the key also implies a possible problem with your design since a PK should uniquely identify each and every row in the table and the same column values for the attributes of the PK should not be appearing in multiple rows. This is not to say the some columns in the table may not have the same value since each column may have a finite domain of values, and some tables particularly event tables hold data where rows may be exactly the same except for an artificial key. But it is something to verify since in your example you are tying to prevent this from happening. I have seen problems like this where artificial keys were used and the data needed to be verifed against business columns before it was allowed to be inserted into the database.

Got questions? Get answers over the phone at Keen.com. Up to 100 minutes free!
http://www.keen.com Received on Sat Jul 22 2000 - 00:00:00 CDT