At 02:21 AM 12-05-01 -0400, you wrote:

> > -----Original Message-----
> > From: Helen Borrie [mailto:helebor@...]
> > Sent: Miércoles 2 de Mayo de 2001 3:21
> >
> > To further confuse, people coming from desktop dbs often think a
> > key and an index are the same thing. They are not. It so
> > happens that one of the things IB does to implement its rules for
> > a primary key is to create a unique, non-nullable index for its
> > column(s). Currently, it implements its rules for a foreign key
> > by creating a non-unique, non-nullable index for it's column(s).
>
>I either misunderstood the posting or there's a typo here.

Yes, correct - it should read "non-unique, nullable index". It's really a misnomer, of course - in longhand, it should be "a non-unique index which allows nulls".

>FKs are supported
>by an underlying index that's non-unique, so it accepts NULL entries.
>Otherwise, you couldn't create non-mandatory, non-identifying relationships
>between entities (tables).

Or, to put it another way, all master-detail relationships would be obligatory. Of course, if you *need* to enforce 1:1 or 1:n then you can't allow null on the index...

> > This particular behaviour for foreign keys isn't always desirable
> > - programmers want more control over indexes that are not on
> > primary key columns - so Firebird is looking at making
> > auto-creation of FK indexes optional.
>
>Yuk, or at least be able to deactivate the FK's index (my current changes to
>be tested privately.)

.. and to be able to have a named index on the FK column(s) whose identity will survive backup and restore...

H.

All for Open and Open for All
InterBase Developer Initiative · http://www.interbase2000.org
_______________________________________________________