---------- Original Message -----------
From: "nathanelrick" <nathanelrick@...>

> Why when i create a Primary Key that is also a foreign key 2 index are
> created ? or i make a mistake somewhere ?

------- End of Original Message -------

http://www.firebirdsql.org/refdocs/langrefupd20-create-table.html#langrefupd20-ct-
using-index (see blue and red notes)

Correct. You cannot share an index between multiple index-using constraints, they
each get their own.

If you were to drop the primary key constraint, and the foreign key were re-using
the primary key's unique index, you'd have a problem: the foreign key would need
a non-unique index after the drop, or it would accidentally still be a unique
foreign key [physical] even though no remaining constraint [logical] required it
to be.

Going the other way, you'd have a primary key using a slight-less-efficient non-
unique index created by the foreign key, and it couldn't rely on the index itself
to magically enforce the uniqueness of the primary key. The two concepts get tied
together internally, such that using a unique index [physical] enforces the
constraint [logical] -- AFAIK Firebird can't enforce a PK without a unique index,
it doesn't have a "backup" mechanism that can use non-unique indices nor do a
full-table-scan/sort/count...

While reference-counting might help in the few situations where multiple indexed
constraints could share indices, I think the use cases for that are rather rare.
Two unique constraints on the same field, or sharing the first few fields of
their definition? Two foreign keys sharing one or several fields? (One being a
superset of the other.) ... Pretty rare.

-Philip