CODEWITHSUNDEEP
firebird
Freddie Bell
Freddie Bell

Reputation: 2287

Cannot build a unique index on a column with no duplicate values

First I ran 

select beatid, count(*)
from beat
group by beatid
having count(*) > 1

And I got no rows as the result.

So then I ran

CREATE UNIQUE INDEX BEAT_IDX_UNQ ON BEAT (BEATID);

and got

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

What's up with that?

Upvotes: 0

Views: 515

Answers (1)

Boki
Boki

Reputation: 657

There can be more causes for situation like yours. First, you need to recheck if you have some pending transactions affecting "beat" relation, committing them or rolling back can resolve the problem. You can check system table rdb$statements for executing queries.

Also, it is question how NULLs are treated in the sense of unique values. You can consult the Firebird NULL Guide, it can be find in the Firebird documentation. After all, if you still have the same problem backup/restore would solve.

Upvotes: 3

Related Questions