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?
Ann W. Harrison answers:
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.