Re: [Firebird-test] Firebird does not create unique constraint.

[Leyne, S. <Se...@br...>]

Mike

> I am using FB RC3 without NPTL support on SLES 9.
> I get the error message "ERROR: attempt to store duplicate value
(visible
> to active transactions) in unique index " when I try to create unique
> index on VarChar column in table with no duplicates. I even emptied
out
> the table and then tried to create the index but got the same message:
> Code:
> ALTER TABLE REFERRAL ADD CONSTRAINT REFERRALBYREFERRALCODE UNIQUE
> (REFERRALCODE);
> Result:
> ERROR: attempt to store duplicate value (visible
> to active transactions) in unique index
> "REFERRALBYREFERRALCODE ".
> I spent about an hour checking system tables and trying to pinpoint
the
> issue. Then I restarted the firebird server and tried again and all
went
> fine. Seems like there was an active transaction that did not let me
do
> it. I did not check outstanding transactions before restart so I dont
> know for sure.

A Firebird index includes index data for all active transactions, this
is by design.

Accordingly, what you are seeing is the correct behaviour.


Sean