sqlsvr wrote:

> One of my data models use natural keys that can change overtime.
> How are foreign keys and cascades implemented in the latest version
> of Firebird? Is it like in a REAL rdbms where the foreign key is
> actually a internal pointer to the referenced table and cascades
> are "instant" no matter how big the database is OR is it a fake
> dbms where the foreign key is duplicated on all referencing tables
> and massive cascades will cause all sorts of locks and crash the dbms?
>

Nothing like putting a little spin on a question.

Firebird implements foreign key constraints as a layer on indexes.
The key value is stored in the referenced table, the referencing
table, and in the indexes on both tables. When a referenced value
changes, the change is identified in the index that implements the
primary or unique constraint on that table, which triggers a change
to the data in the referencing table, and of course to its index.
When a key value is stored in the referencing table, the index on
that key forces a lookup in the index of the referenced table to
insure consistency.

So in your vernacular, Firebird is a "fake dbms" - without hard
internal links and with duplicated data. I've worked on a the
internals of few databases (like six) and they were all "fake"
in that sense.

Since Firebird uses MVCC rather than record locking, a cascading
change will not cause "all sorts of locks" - or any sorts of
locks. Conflicting cascades from different transactions could
cause update conflicts, just as any other type of change might.
Cascading changes to foreign keys will not crash Firebird.

And, though you didn't ask, if one transaction changes a
referenced key while a concurrent transaction attempts to add
a referencing key of the old value, Firebird will detect the
conflict and reject the change.

Best regards,


Ann