At 10:31 AM 14/12/2003 +0700, you wrote:

>Sorry, what I meant was "by using a unique index, don't we effectively
>achieve uniqueness of column"?

Yes. So, if all you want is uniqueness, you only need the index.

>True, FB still requires unique constraint
>declaration (not unique index), and that's what I am questioning. If a
>column is being uniquely indexed, why doesn't the column automatically
>become eligible for being referenced by an FK?

Because an index is not a constraint and a constraint is not an index. A
constraint has rules, one of which is that it requires a unique index to
enforce a rule.

> > It's there to
> > -- cater for people like yourself who want to reference columns other than
> > the PK in FK relationships. Dependencies are created on keys, not indexes.
>
>Okay, but what is the [effective, visible] difference between a unique
>index on a column and a unique key (not PK)?

One is that you can can name an index anything you like, whereas (by
tradition) the system-created index enforcing a constraint got a mandatory,
nasty, system-generated name. OK, 1.5 has made the naming of constraints
and their indexes much nicer - you can now incorporate a USING clause in a
constraint definition to assign your own name to a constraint's index,
while the new default behaviour is for the system to apply the name of the
constraint as the index's identifier. It's not so long ago in IB's history
that the names of sys-generated indexes did not survive a restore, so
explicit plans got broken if you used the constraints' indexes in them.

Another is that you can't disable a constraint's index using ALTER INDEX
inactive; and you can't delete it, either. You have to drop the constraint.

/heLen