Nando,

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

Did a quick test ... which I should have done before, of course.

CREATE TABLE INDEX_TEST
(
C1 INTEGER
);

COMMIT;

CREATE UNIQUE ASC INDEX I_IND_TEST ON INDEX_TEST (C1);

COMMIT;

insert into index_test values (4);
-- works

insert into index_test values (null);
-- works

insert into index_test values (null);
-- works again!!

insert into index_test values (4);
-- fails with:
attempt to store duplicate value (visible to active transactions) in unique
index "I_IND_TEST"

> And, confusion between keys and indexes is surely one of the top 5

misconceptions

> heard at database classes!

True :-)

Maybe it has something to do with many engines using indices to implement
constraints.


With regards,

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