List:General Discussion« Previous MessageNext Message »
From:Christian Mack Date:September 8 1999 3:20pm
Subject:Re: multiple-column unique constraint
View as plain text  
Paul Schreiber wrote:
> I have a table like so:
> CREATE TABLE absentee_bid (
>   ab_id int(16) DEFAULT '0' NOT NULL auto_increment,
>   reg_id int(16) DEFAULT '0' NOT NULL,
>   sale_number varchar(255) NOT NULL,
>   auction_number varchar(255) NOT NULL,
>   lot_description varchar(255) NOT NULL,
>   bid_amount varchar(255) NOT NULL,
>   last_mod timestamp(14) NOT NULL,
>   KEY (reg_id),
>   KEY (sale_number),
>   KEY (bid_amount),
>   PRIMARY KEY (ab_id),
>   UNIQUE idx_ab_id (ab_id)
> );
> I want to ensure the three-column combination of reg_id, sale_number and
> auction_number is unique. What's the best way to enforce this constraint?
> Paul

Hi Paul

Your Problem is, that sale_number and auction_number are both 255 chars/bytes long.
You can only have 16 keys with up to 15 fields in it (no problem here) and up to 256 Bytes
So you have to shorten these fields like:
  sale_number varchar(126) NOT NULL,
  auction_number varchar(126) NOT NULL,

Now you have 4Bytes for the INT and 2 * 126Bytes for the VARCHAR's = 256Bytes.
With this change just use:
  UNIQUE reg_sale_auction (reg_id,sale_number,auction_number)

Note: You can shrink the VARCHAR's different, but you have to consider the overall length
of the key.

A PRIMARY KEY is already an UNIQUE key, so you don't need the idx_ab_id one.


PS: Sorry for the late answer, I was really busy.

multiple-column unique constraintPaul Schreiber17 Aug
  • Re: multiple-column unique constraintMartin Ramsch17 Aug
  • Re: multiple-column unique constraintChristian Mack8 Sep