Re: [Firebird-devel] RFE: Allow control over constraint indices

[Daniel R. <da...@ac...>]

Hi,

At April 27, 2005, 13:40, Woody (TMW) wrote:

> From: "Daniel Rail" <da...@ac...>
>>
>> In example:
>> A simple CREATE INDEX index1 would set the reference count to one upon
>> creation. And, when adding a FK to the same table and it can use the
>> index1, then the reference count would be incremented to 2. And,
>> at this point a user wouldn't be allowed to execute DROP INDEX index1,
>> because the reference count is above one.
>>

> What difference does a reference count make for an index? It doesn't make
> much sense to me. At most, there would only be 2 reasons for the index,
> faster searching and FK/PK enforcement. Faster searching isn't something
> that is a "necessity", per se. Even if you created the index manually at
> first, if you add the ability for a constraint to use a per-existing index
> when creating the constraint, how does FB know that you still plan to use
> the index outside the constraint context? And what difference would it make
> anyway?

I simply gave an example.

Here's another example:
You create a PK on field F1 and then an FK on the field F1, within the
same table. The PK is just to reinforce the uniqueness within the
table and the FK points to a master table. Here the same index would
be used for the PK and the FK.  So, if there wouldn't be any reference
count on the index, and you drop the PK, do you try to drop the
index at the same time?  With a reference count you would know almost
immediately that the index cannot be dropped and the index stays
intact.

So essentially, an index could be created by a user, then subsequently
used by a FK and/or a PK.  Also, an index created by a FK could also
be used by a PK, and vice-versa.

The reference count is simply to know when the index could actually be
dropped.

-- 
Best regards,
 Daniel Rail
 Senior System Engineer
 ACCRA Group Inc. (www.accra.ca)
 ACCRA Med Software Inc. (www.filopto.com)