johnmancuk wrote:

>My understanding is that you can't use sprocs, triggers or check
>constraints to maintain referential integrity on FB (at least
>reliably, in a multi-transaction situation) because they don't
>use 'magic' to see through your current transaction context. Indexes
>do.
>
>Hence Indexes are essential for RI constraints, even when
>selectivity is poor and they will never be used by the optimizer. I
>would guess that's the reason they're make automatically for all
>FK's.
>
>(for example, someone in another transaction could have already
>deleted the record your FK refers to. Your sproc/trigger will be
>none the wiser. But the index will allow you to 'see' the change,
>and will stop your insert)
>
>Of course I could be totally wrong!
>
>John
>
>
>

John,

The Idea to use triggers to enforce referential integrity is to avoid
poor selectivity indices.

If you have a lookup table with just a few values (the states of your
country for example, you could have thousands, milions of costumers, but
just a dozen, or a few dozens states). This case will be better if you
not use declarative referencial constraints (declared on tables) but
enforce it via triggers.

You are right to tell that someone delete a record your transaction
couldn't see it, but these kind of tables are virtually static, so you
won't have this case.

see you !

--

Alexandre Benson Smith
Development
THOR Software e Comercial Ltda.
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br