Hi all,

I understand that Firebird always creates an index on the "child" table for
a foreign key.

I believe that index creation could be avoided at the following cases :

A) There is allready an index with the same fields

Example : 1 to 0 - 1 relationship.

create table PARENT
(
PARENT_ID INTEGER not null,
PARENT_FIELD1 ........ ,
constraint PK_PARENT primary key (PARENT_ID)
);

create table CHILD
(
PARENT_ID INTEGER not null,
CHILD_FIELD1 ......... ,
constraint PK_CHILD primary key (PARENT_ID)
);

alter table CHILD
add constraint FK_CHILD_PARENT foreign key (PARENT_ID)
references PARENT;



B) There is allready an index starting with the same fields

Example : identifing relationship.

create table PARENT
(
PARENT_ID INTEGER not null,
PARENT_FIELD1 ......... ,
constraint PK_PARENT primary key (PARENT_ID)
);

create table CHILD
(
PARENT_ID INTEGER not null,
CHILD_NO INTEGER not null,
CHILD_FIELD1 ........ ,
constraint PK_CHILD primary key (PARENT_ID, CHILD_NO)
);

alter table CHILD
add constraint FK_CHILD_PARENT foreign key (PARENT_ID)
references PARENT;


C) Parent table is rarely modified.

Example :

create table COUNTRY
(
COUNTRY_ID INTEGER not null,
COUNTRY_NAME VARCHAR(20),
constraint PK_COUNTRY primary key (COUNTRY_ID)
);

create table CUSTOMER
(
CUSTOMER_ID INTEGER not null,
COUNTRY_ID INTEGER not null,
CUSTOMER_NAME VARCHAR(20),
constraint PK_CUSTOMER primary key (CUSTOMER_ID)
);

alter table CUSTOMER
add constraint FK_CUSTOMER_COUNTRY foreign key (COUNTRY_ID)
references COUNTRY;