|Subject||Re: [ib-support] Unique constraint not an index? was Re: Error:Object is in use|
> >why should IAlthough it is hard to imagine database system without indexes,
> >bother to create a unique constraint if it's all-same-different if I make
> >a unique index instead? Is this "optionality" there to service some sort
> >of backward compatibility?
> Or why should you create a unique index if a unique constraint has the
> same effect? SQL data definition semantics tend toward redundancy,
> 'garters & panty hose' if you like.
the fact is that SQL92 does not know them.
Standard SQL language describes what results you are interested in
(like that you want values in column to be unique=unique constraint),
but does not care about implemetation details (like that you want
to create index to speed up query or to enforce uniqueness).
Indexes are vendor specific extensions (I am not sure about SQL99),
they are just auxiliary structures intended to speed up some operations.
That said, I would prefer creating unique constraint instead of unique
index, because it is more standard way (unless I need to use index name
in explicit plan, which I try to avoid anyway).
(sorry for too late reply; our office is moving, and we are without
phone/internet some time)