| Subject | Re: [firebird-support] Where to put my indexes? |
|---|---|
| Author | Helen Borrie |
| Post date | 2010-11-20T22:23:44Z |
At 10:18 AM 21/11/2010, you wrote:
It is at least worth considering to use a generated surrogate key as the PK where you would otherwise have a multi-key index, especially if you plan to master that PK to foreign keys. You can use a unique constraint (or just a unique index) over the surrogated keys to enforce the uniqueness.
Also ask about the concept of "atomicity" in keys, if you haven't heard of it.
./heLen
>Dear ./heLen!Primary Key is a constraint. The Firebird engine automatically creates a unique index (ascending by default) to support the PK. It is no different in its effect to any other unique index.
>
>Thank you for the explanation.
>
>One more question though: is there a difference between primary keys or "normal" indexes?
>Do the rules you wrote apply also to primary compound keys?Recommendations, not "rules". If you have to have a compound index for your PK, then the leftmost keys of that index will be available to the optimizer.
It is at least worth considering to use a generated surrogate key as the PK where you would otherwise have a multi-key index, especially if you plan to master that PK to foreign keys. You can use a unique constraint (or just a unique index) over the surrogated keys to enforce the uniqueness.
Also ask about the concept of "atomicity" in keys, if you haven't heard of it.
./heLen