Hi,

> >> M> As far as I can remember, IB and Fb1 did allow one NULL into an
> >> M> unique INDEX, but not in a constraint. I might be wrong there

though.

> >>
> >> no, not even one.
>
> M> Did a quick test ... which I should have done before, of course.
>
> <SNIP>
>
> what you have found is documented SQL and Firebird 1.5 behaviour, AFAIK.
> You did test with Fb 1.5, I hope!
> With previous versions you should get an error here:
>
> M> CREATE UNIQUE ASC INDEX I_IND_TEST ON INDEX_TEST (C1);

Yes, 1.5... But I thought this was only with Unique _Constraints_ :-)

Anyways - the odd thing: IB let's you CREATE an unique index like the above,
but returns an error when trying to insert NULL.

> M> Maybe it has something to do with many engines using indices to

implement

> M> constraints.
>
> Sure it has. The motto "keys aren't indexes and indexes aren't keys"
> is there in my toolbox right beside "null is a state, not a value".

*g*

And guess what ... The error returned by IB7:

attempt to store duplicate value (visible to active transactions) in unique
index "I_IND_TEST" null segment of UNIQUE KEY


(notice the "UNIQUE KEY" part :-)
With regards,

Martijn Tonies
Database Workbench - the developer tool for InterBase & Firebird
Upscene Productions
http://www.upscene.com