--- In firebird-support@yahoogroups.com, "Ann W. Harrison"
<aharrison@i...> wrote:

> Yes, I think so, thought given it's a 1.03 problem and people are very
> focused on 2.0 and Vulcan, it may not get the attention it deserves.
>
> Does your application normally generate duplicates on the primary key
> and expect the database to reject them? That's OK - I'm just

trying to

> imagine how the problem occurred.

These records are created / updated with the following bit of code in
a stored procedure.

IF (EXISTS (SELECT LDSI_STOP_ID, LDSI_ITEM_NUMBER
FROM LD_STOP_ITEM
WHERE LDSI_STOP_ID=:LDSI_STOP_ID AND
LDSI_ITEM_NUMBER=:LDSI_ITEM_NUMBER
PLAN (LD_STOP_ITEM INDEX(RDB$PRIMARY40)))) THEN
BEGIN
UPDATE LD_STOP_ITEM
SET LDSI_PIECES=:LDSI_PIECES, LDSI_WEIGHT=:LDSI_WEIGHT,
LDSI_METRIC=:LDSI_METRIC, LDSI_DESC=:LDSI_DESC,
LDSI_RATE=:LDSI_RATE, LDSI_AMOUNT=:LDSI_AMOUNT,
LDSI_KEYWORD=:LDSI_KEYWORD
WHERE LDSI_STOP_ID=:LDSI_STOP_ID AND
LDSI_ITEM_NUMBER=:LDSI_ITEM_NUMBER;
END ELSE
BEGIN
INSERT INTO LD_STOP_ITEM
(LDSI_STOP_ID, LDSI_ITEM_NUMBER, LDSI_PIECES, LDSI_WEIGHT,
LDSI_METRIC, LDSI_DESC, LDSI_RATE, LDSI_AMOUNT,
LDSI_KEYWORD)
VALUES (:LDSI_STOP_ID, :LDSI_ITEM_NUMBER, :LDSI_PIECES,
:LDSI_WEIGHT,
:LDSI_METRIC, :LDSI_DESC, :LDSI_RATE,
:LDSI_AMOUNT,
:LDSI_KEYWORD);
END

where LDSI_STOP_ID is a generator derived primary key from another
table and LDSI_STOP_ITEM is a sequentially assigned item number that
starts at 1 for each LDSI_STOP_ID.

This is probably sub-optimal SQL but I would still expect it to fail
if somone created a duplicate record between execution of the IF
EXISTS and the INSERT.