Automatic creation of foreign key indices?

[Jon Blower]

Hi all,

Does H2 automatically create indices for foreign key constraints? I'm
not creating FK indices directly but I do notice them appearing in the
"indexes" section of the table in question in the H2 console.

So if I have a table:
create table foo (
id identity primary key,
bar_id varchar not null, /* FK reference to another table */
constraint foo_fk1 foreign key (bar_id) references bar(id)
);

is it correct to assume that H2 will automatically create an index on
the bar_id column of the above table? So the following statement
would be redundant:
create index foo_idx1 on foo(bar_id); ?

Thanks for any help,
Jon

--
--------------------------------------------------------------
Dr Jon Blower Tel: +44 118 378 5213 (direct line)
Technical Director Tel: +44 118 378 8741 (ESSC)
Reading e-Science Centre Fax: +44 118 378 6413
ESSC Email: j...@mail.nerc-essc.ac.uk
University of Reading
3 Earley Gate
Reading RG6 6AL, UK
--------------------------------------------------------------

[Thomas Mueller]

Hi,

> Does H2 automatically create indices for foreign key constraints?

Yes: The required indexes are automatically created if required. I
will add this to the docs.

> is it correct to assume that H2 will automatically create an index on
> the bar_id column of the above table? So the following statement
> would be redundant:
> create index foo_idx1 on foo(bar_id); ?

Yes. But if you create this index before creating the foreign key, it
will be used and no additional (system-) index will be created.

Regards,
Thomas