Hello list,

I have a huge problem with duplicate keys.

It is a little bit dificult for me to explain in english. I hope you
can understand what I meen. It is also al little bit long, but it is
very important for me to solve this problem. It must be a bug in
firebird.

I have a table in which I store the turnover of a customer. Per
customer and year and month is one record in the table.
The table looks like this:

create table kuu
(kuu_id integer not null /* unique id */
primary key,
kud_id integer /* customer id */
not null,
jahr integer /* year */
not null,
monat smallint /* month */
not null,
i_umsatz numeric (12, 2)) /* turnover */

Ther is an index on the table:

create unique index kuu_kud_id_jahr_monat on kuu (kud_id, jahr, monat)

In the after insert and after update and after delete trigger of the
invoice table ther is a call of a stored procedure, which updates the
table kuu.

It looks like the following (I dropped everything which is not
important to this problem):

create trigger fak_insert for fak
before insert as

begin
execute procedure kuu_setzen (new.customer_id, new.invoicedate,
new.invoice_value);
end ^

The stored procedure is like that:

create procedure kuu_setzen (kud_id integer,
datum date,
invoice_value numeric (12, 2)) as

declare variable kuu_id integer;
declare variable jahr integer;
declare variable monat integer;

begin
jahr = extract (year from :datum);
monat = extract (month from :datum);
select kuu_id
from kuu
where kud_id = :kud_id and
jahr = :jahr and
monat = :monat
into :kuu_id;
if (:kuu_id is null) then
begin
if (:netto_umsatz <> 0) then
insert into kuu (kud_id, jahr, monat, i_umsatz)
values (:kud_id, :jahr, :monat, :invoice_value);
end
else
begin
if (:netto_umsatz <> 0) then
update kuu
set i_umsatz = i_umsatz + :netto_umsatz
where kuu_id = :kuu_id;
end
end


Now I have a programm which is converting data from a btrieve database
to the new firebird database.
I read one invoice after the other and insert it to the firebird
database.
After all is finished without any errormessage, I have every invoice
stored and the turnover is stored in the table kuu.
For example i have 10 invoices for one customer in one moth of a year.
Then there are two records in kuu for this customer for this month of
this year, which normale must not be, because of the unique index.
When I summ the values for i_umsatz of this two record, I get the
right value for all invoices.


When I try to read the records, there is only one record shown. Then I
switch the index to not unique, there are two records shown which the
same kud_id, jahr and monat.

I didn't see this problem, until I did a backup and restore of the
whole database today. When I did the restore, I got an error for the
duplicate keys.

I think the database should avoid such things like storing duplicate
keys first and then show an error when you want to restore the
database.

The strange thing is also, that the stored procedure does right for a
several time and updates the right record in the kuu table for 5 or 6
times. Then it doesn't find the record and therefore it inserts a new
one. When you than make a select on the table kuu like that:

select * from kuu where
kud_id = 95 and jahr = 2001 and monat = 5

you get only one record. Then you change the uniqe state of the index
on (kud_id, jahr, monat) to non unique and make the fetch again, you
get 2 records.

Trying to set the unique flag again leeds to an error (unique key
error).

Could anobody tell me, what is the reason for this problem?

cu
Helmut Steinberger