db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mamta Satoor" <msat...@gmail.com>
Subject Re: Foreign key -- implicit index?
Date Mon, 19 Mar 2007 16:59:43 GMT
Dan, I haven't tried this myself. I thought if you name your constraint,
then I thought that name becomes the name of the backing index. But when I
check the documentation of CREATE INDEX, it has following information which
seems to say that backing index name is always system generated irrespective
of you named your constraint or not. Maybe someone else knows the exact
answer but in the meantime, you might want to try it yourself using
following query to see what is the name of the backing index for a named
constraint. Hope this helps a little.

**************
Unique, primary key, and foreign key constraints generate indexes that
enforce or "back" the constraint (and are thus sometimes called *backing
indexes*). If a column or set of columns has a UNIQUE or PRIMARY KEY
constraint on it, you can not create an index on those columns. Derby has
already created it for you with a system-generated name. System-generated
names for indexes that back up constraints are easy to find by querying the
system tables if you name your constraint. For example, to find out the name
of the index that backs a constraint called FLIGHTS_PK:

*SELECT CONGLOMERATENAME FROM SYS.SYSCONGLOMERATES,SYS.SYSCONSTRAINTS
WHERESYS.SYSCONGLOMERATES.TABLEID = SYSCONSTRAINTS.TABLEID
AND CONSTRAINTNAME = 'FLIGHTS_PK'*

***************

On 3/18/07, Dan Karp <dkarp@zimbra.com> wrote:
>
> > > Just to make sure, this foreign key declaration
> > >
> > >    CONSTRAINT fk_mail_item_parent_id FOREIGN KEY (mailbox_id,
> parent_id)
> > >      REFERENCES ${DATABASE_NAME}.mail_item(mailbox_id, id),
> > >
> > > implicitly creates an index on (mailbox_id, parent_id), right?
> >
> > Yes, that is correct. Copied following from the CREATE INDEX page in
> > Derby's documentation:
> >
> > Unique, primary key , and foreign key constraints generate indexes
> > that enforce or "back" the constraint (and are thus sometimes called
> > backing indexes ).
>
> Is there any way to explicitly assign a name to this backing index?  In my
> case, I want to force the optimizer to choose the backing index using "--
> DERBY-PROPERTIES index=".
>

Mime
View raw message