Uniqueness violated on a composite key

[Marco]

Hi

The last of the following statements generates the error "Uniqueness of index ORDPROD is violated". I don't understand why since both the fields are in the primary key. Am I missing something? I'm using the MS Visual FoxPro ODBC driver v. 6.00.8167.00

CREATE TABLE `C:\TBL_LINEITEM`

ORDERID I NOT NULL,
PRODUCTID I NOT NULL,
PRIMARY KEY ORDERID + PRODUCTID FOR NOT DELETED() TAG ORDPRO


insert into TBL_LINEITEM values (1, 1
insert into TBL_LINEITEM values (1, 2
insert into TBL_LINEITEM values (1, 3
insert into TBL_LINEITEM values (2, 2

Thanks
Marc

[Trey Walpole]

insert ...(1,3) makes an index value of 4, as does (2,2), hence the
violation

change the pk to

PRIMARY KEY STR(ORDERID)+STR(PRODUCTID) etc.

now, this means that to use the index in queries, the query would need to
have, e.g.

where STR(ORDERID)+STR(PRODUCTID)=str(1)+str(3)

Post by Marco
Hi.
The last of the following statements generates the error "Uniqueness of

index ORDPROD is violated". I don't understand why since both the fields are
in the primary key. Am I missing something? I'm using the MS Visual FoxPro
ODBC driver v. 6.00.8167.00.

Post by Marco
CREATE TABLE `C:\TBL_LINEITEM`
(
ORDERID I NOT NULL,
PRODUCTID I NOT NULL,
PRIMARY KEY ORDERID + PRODUCTID FOR NOT DELETED() TAG ORDPROD
)
insert into TBL_LINEITEM values (1, 1)
insert into TBL_LINEITEM values (1, 2)
insert into TBL_LINEITEM values (1, 3)
insert into TBL_LINEITEM values (2, 2)
Thanks,
Marco

[Fred Taylor]

Rather than just STR(), you should probably use BINTOC(). The index would
take up less space as STR() would make a 10 character string for each part
(total 20 chars) and BINTOC() would take up 4 each for a total of 8.

Fred
Microsoft Visual FoxPro MVP

Post by Trey Walpole
insert ...(1,3) makes an index value of 4, as does (2,2), hence the
violation
change the pk to
PRIMARY KEY STR(ORDERID)+STR(PRODUCTID) etc.
now, this means that to use the index in queries, the query would need to
have, e.g.
where STR(ORDERID)+STR(PRODUCTID)=str(1)+str(3)

Post by Marco
Hi.
The last of the following statements generates the error "Uniqueness of

index ORDPROD is violated". I don't understand why since both the fields are
in the primary key. Am I missing something? I'm using the MS Visual FoxPro
ODBC driver v. 6.00.8167.00.

Post by Marco
CREATE TABLE `C:\TBL_LINEITEM`
(
ORDERID I NOT NULL,
PRODUCTID I NOT NULL,
PRIMARY KEY ORDERID + PRODUCTID FOR NOT DELETED() TAG ORDPROD
)
insert into TBL_LINEITEM values (1, 1)
insert into TBL_LINEITEM values (1, 2)
insert into TBL_LINEITEM values (1, 3)
insert into TBL_LINEITEM values (2, 2)
Thanks,
Marco

[Olaf Doschke]

Post by Marco
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:

3. An index must have a fixed length.
STR(ORDERID)+STR(PRODUCTID) and
BINTOC(ORDERID)+BINTOC(PRODUCTID)
are such fixed length expressions. Don't come up with
the idea of saving space with
TRANSFORM(ORDERID)+TRANSFORM(PRODUCTID),
that won't work, not just because '1'+'23' == '12'+'3'.

4. When using BINTOC() you MUST create and use the index
with SET COLLATE TO MACHINE. Otherwise two different
number pairs would be handled as being the same, although they
do not violate the uniqueness of the index! The reason is: In a
collate sequence some chars or pairs of chars are sorted as if
being another char.

5. Don't use a filtered index (FOR NOT DELETED()),
this will cost you performance. Better: a) make a separate index
just on DELETED() or b) negate the values of ORDERID and
PRODUCTID before deleting a record. With an index on
BINTOC(ORDERID)+BINTOC(PRODUCTID) that would
sort these records above all undeleted records. Maybe c) an
index on IIF(DELETED(),BINTOC(-ORDERID)+BINTOC(-PRODUCTID),;
BINTOC(ORDERID)+BINTOC(PRODUCTID)) could help,
because this way you won't need to negate the values yourself.

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.

Bye, Olaf.

[Fred Taylor]

Post by Olaf Doschke

Post by Marco
PRIMARY KEY ORDERID + PRODUCTID FOR NOT DELETED() TAG ORDPROD

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.
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

[Olaf Doschke]

Post by Fred Taylor
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

Post by Fred Taylor
in my book, BINTOC() wins over STR() in both size and speed.

OK, I hadn't made such an in depth analysis :-)

Just one more tip:

When you want to select some records with the same pair of
IDs, it was much faster for me to do this:

var3 = BINTOC(var1)+BINTOC(var2)
SELECT * FROM sometable WHERE BINTOC(field1)+BINTOC(field2) = var3

then this:
SELECT * FROM sometable WHERE BINTOC(field1)+BINTOC(field2);
= BINTOC(var1)+BINTOC(var2)

It depends of course of the number of records in the table and in the
result,
but it's not hard to guess, why making the conversion once is faster then
doing
so for every record...

So tip 7 is: Wherever you seek for the same bintoc(somevalue) again,
store it in a variable, and use that in WHERE-Clauses or SEEKs.

The thing why I assumed BINTOC() to be slower is: It was such a performance
difference to compute BINTOC() only once, that I thought: Ohoh, BINTOC()
is reeeaaally slow. And I couldn't imagine, that STR() was even slower.

Bye, Olaf.