Helen Borrie wrote:

>These are two separate problems.
>-- In the first case, I think one always has to weigh up the relative costs
>and benefits if one tries to take a generic approach to design. I think
>you are in double-triple jeopardy if you aim to make a client/server
>database with standard language characteristics behave like Access, a
>file-server data storage system with its own peculiar language.
>
>
>

Theres always other options, like dont put triggers or stored procedure
and solve it in a middle tier. That approach is that i use. I dont like
Access, but maybe i must use. By example my last installation of
software MUST be whith access, customer election. I just can´t modify
that requeriment or i just dont qualify.
I use ASTA to resolve that tier, and perform excelent in both cases
(Access and Fierbirdd), except the foreign key trouble.

>In the second case, where you might need to use different languages to
>express the same "thing", it is not a good design strategy to make that
>"thing" a key. A better alternative is to provide a surrogate key and
>enable name and language changes to occur without breaking any
>relationships. That makes the relationship more robust, can be implemented
>in any DBMS and enables you to avoid declarative referential integrity if
>there's dbms-specific reason to avoid them (as there is in Firebird with
>small lookup tables and/or imbalance in the distribution of values in a
>foreign key).
>
>
>

Obviously my poor english make the mistake. Is exactly what i do, the
foreign key have a ID and a DESC field. That is what your mind or im
missing something?

>>Anyway, why that work fine in Access and not in Firebird? Something is
>>wrong there.
>>
>>
>
>What is "wrong" is that Access is architecturally a totally different beast
>to Firebird. It stores indexes in database files.
>
>

I hate Access and love Firebird. But the true is Access outperform
Firebird in that case, and really i dont feel is my mistake the way is
implemented that table with foreign keys. I have a deep know of
integrity, and keep the rules rigth. It work fine using other db engines
(Oracle, and MSSQL is where i try). gaian, my tougth are only in the way
to make Firebird the best DB in the world.

>>So the solution can be deleting that keys?
>>
>>
>Yes. It is not the *key*, per se, that causes the trouble, it is the
>mandatory automatic index that is created to support the key. So, if you
>are not looking at static conditions that you can implement with check
>constraints, then you are looking at implementing referential integrity
>with custom triggers. This is simpler than you might think. The triggers
>you write will be identical to the checks that the formal RI triggers
>do: they are quite consistent for each table that you need to work
>on. Yes, it does mean you have to do it for each of these low-number
>lookup/control tables but you can write boilerplate code..
>
>Once you get accustomed to the power of triggers, you might be less
>inclined to treat it as a problem that you can't imitate everything that
>Access does. Firebird isn't Access and it isn't *like* Access. The move
>from a dbms that manages files to one that manages on-disk structures is
>one that takes a lot of rethinking.
>
>
>

Well, i use lookups inside the application (Delphi) so can put the
checks inside the middle tier in ASTA, and remove that foreign keys.
Can you tell me if the next version of firebird will change that or will
remain in the same way?

TIA.

German.