Unique Constraint

[Alan McDonald]

Not sure if I'm getting the correct error message or why I can't add a
unique constraint:

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



Now I know the values in the field in question are unique - I have only 3
records.

But when I delete all the records from the table, I still get the above
message.



So this message appears not to relate to the constraint but instead to the
RBD$INDICES insert. But the name of the UNQ_ index is not already taken.
Indeed, I have tried several other names. I have only a handful of unique
indexes anyway and can see no conflict.



Does anyone have a hint as to why an empty table should not accept and
unique constraint with an index name which does not already exist?



Regards

Alan McDonald

[Alan McDonald]

Not sure if I'm getting the correct error message or why I can't add a
unique constraint:

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



Now I know the values in the field in question are unique - I have only 3
records.

But when I delete all the records from the table, I still get the above
message.



So this message appears not to relate to the constraint but instead to the
RBD$INDICES insert. But the name of the UNQ_ index is not already taken.
Indeed, I have tried several other names. I have only a handful of unique
indexes anyway and can see no conflict.



Does anyone have a hint as to why an empty table should not accept and
unique constraint with an index name which does not already exist?



Regards

Alan McDonald





Something was being cached - it needed a server restart. Disconnecting and
re-connecting to the database did not clear the cache. Doesn't seem right to
me

Even isql returned the same error message.

Alan

[Leyne, Sean]

Now I know the values in the field in question are unique - I have only 3 records.
But when I delete all the records from the table, I still get the above message.

<SL> Are there other active connections? Have they committed all transactions?

<SL> FB Indexes contain information for **all active transactions**, so if there are active transactions which can still see the old data ... adding the new constraint would fail.

[Alan McDonald]

Now I know the values in the field in question are unique - I have only 3
records.

But when I delete all the records from the table, I still get the above
message.



<SL> Are there other active connections? Have they committed all
transactions?



<SL> FB Indexes contain information for **all active transactions**, so if
there are active transactions which can still see the old data . adding the
new constraint would fail.



Thanks Sean,

I didn't think so but it's possible - I'll defer on this one.
Alan