>Hi Gang!
>
>While preparing the final steps to migrate from dBase/FB 1.5 to C#/FB
>2.5 I need to create a unique constraint for the old databases, and
>need some help from someone still familiar with 1.5.
>
>Not using uniques too often, so it's probably something I am doing
>basically wrong, but I can't create the following unique constraint:
>
> alter table T
> add constraint UNQ1_T
> unique (F1, F2)
> using index UC_T
>
>I receive the following error:
>
>Invalid insert or update value(s): object columns are constrained - no 2 table
>rows can have duplicate column values.
>attempt to store duplicate value (visible to active transactions) in unique
>index "@1".
>
>I checked that at this moment the table should be "clean" from
>duplicates by using the following statement:
>
> select
> T.F1,
> T.F2,
> count(T.ID)
> from
> T
> group by
> 1, 2
> having
> count(T.ID) > 1
>
>The result after the first run was a bunch of rows which would have
>violated the unique. I cleared them off using a "For select..." in a
>stored proc and subsequent runs yield only empty rowsets.
>So the table should be ready for the unique shouldn't it?
>
>Can somebody help me to see the forest with all these trees around,
>please? What else could stop a unique like this? If I make a backup of
>the database and restore it to a FB 2.5 server, the unique is created
>just fine. The old server is Classic Engine, if that should matter.
Two thing spring to mind, André, neither of them particular to 1.5.
1) You don't want to use indexes in your select (errors in the index may give
wrong results), so try:
select
T.F1+0,
T.F2+0,
count(T.ID+0)
from
T
group by
1, 2
having
count(T.ID+0) > 1
(you probably do not need all of the +0s, I just don't know which of them you
don't need)
2) Are you sure no-one else is connected to the database and that you committed
after your "For select..." (if there are still active transactions that started
before the one that deletes committed, I'm not surprised if the unique
constraint fails - although I don't know how Firebird handles such a situation)?
HTH,
Set
