Re: Uniqueness violated on a composite key

From: Fred Taylor (ftaylor_at_mvps.org!REMOVE)
Date: 05/11/04


Date: Tue, 11 May 2004 08:18:49 -0700


"Olaf Doschke" <b2xhZi5kb3NjaGtlQHQtb25saW5lLmRl.strconv.14@t-online.de>
wrote in message news:uo%23rcizNEHA.1272@tk2msftngp13.phx.gbl...
> > PRIMARY KEY ORDERID + PRODUCTID FOR NOT DELETED() TAG ORDPROD
> As the others have already said:
> 1. this won't of course work, since 1+3 and 2+2 are both 4
> and you get a violation of the key.
> 2. an expression like STR()+STR() or BINTOC()+BINTOC() is usually used
> for such a compound key.
>
> Additional tips:
> 6. BINTOC() makes a shorter index (4 bytes compared to 10 bytes
> for STR(), but it also takes much longer to be computed, so an
> index using STR() could make a better performance. test it, there
> isn't really a rule of thumb when which is better.

Actually in my testing, BINTOC() was significantly faster than STR(). For
1,000,000 conversions, STR took .706 seconds, while BINTOC() took only .489
of a second. Further tests produced even wider margins for BINTOC() vs
STR() (roughly 30% faster in every run), but even with those small deltas,
you'd have to have millions of records before it became truly noticable. So
in my book, BINTOC() wins over STR() in both size and speed.

Fred
Microsoft Visual FoxPro MVP