---------- Original Message ----------- From: "nathanelrick" <[email protected]> > 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
