features supported are:
- on-update/delete restrict
- on-update/delete noaction
- on-update/delete setnull
- on-update/delete cascade
- online add/drop foreign key
The foreign keys are implemented inside data-nodes, and will hence work regardless of which type of client you use. SQL, memcache, Cluster/J, ndbapi, {insert your favourite connector}.
somewhat interesting differences between ndb and innodb are:
- i haven't implemented the Innodb "extension" has that allows parent reference to be non-unique. It seems ok i think...wl#148 describes it as a feature not to have this extension
- For innodb "noaction" is identical as "restrict".
For ndb, noaction means deferred check. I.e the constraint is checked before commit.
(i think it should be at end of statement...but ndb currently doesnt have any statement concept)
- Ndb does not "natively" support update of primary keys (i.e not inside data nodes), an update on a primary key from SQL is emulated by an delete+insert.
The implication of this, is that on-update-actions (such as cascade/setnull) doesn't work if parent reference is using primary key.
Fixing this...means first adding native support for update of primary keys, and then adding the FK support for it...but I think(hope) that this is a limitation that is acceptable.
- Ndb today supports checking unique keys in a deferred fashion.
This feature is used by slave applier to avoid false conflicts with replication.
Foreign keys supports the same.
- Ndb implementation does not (today) support the foreign_key_checks variable.
- Ndb supports online adding and dropping of foreign keys (i.e no table copy, clients can read/write while FK is added/dropped).
Hopefully this fact can decrease the need for the foreign_key_checks variable. (although we should consider how to process a mysqldump-file without hazzle)
implementation note 1: the implementation is based on ndb's internal trigger mechanism. the same mechanism that is used to maintain unique indexes. one implication of this is that cost (both in terms of single threaded latency and increase in load) should be roughly comparable to that of unique indexes. note that (to my knowledge) no benchmarks has been performed. stay tuned for that.
implementation note 2: a somewhat interesting implementation discovery is that mysql actually features a capable foreign key parser. my original plan was to "copy" the innodb hand-crafted parser. but this wasn't needed...the server parsed and created nice structures...only didn't expose them to the storage engine. so instead of adding a hand-crafted parser, i modified the server to expose them, and the code in our handler was much simpler...happy with that!
implementation note 3: a (deliberate) artefact of current implementation is that when using on {update/delete} {set null/cascade} the set-null or cascade that is performed on child table, will be put into binlog (if turned on). that means that the foreign key action will be performed on slave side too, even if foreign keys are not present (or enabled) on that site.
a general reflection conceived when adding this is that ndb internals now has added quite a lot of infrastructure that really makes adding complex features like this a lot easier.
Other resources on the subject is:
and finally the diffstat for the original quilt patchset looks like
[]:jonas@eel:~/src/ndb-fk> diffstat patches/*
102 files changed, 14813 insertions(+), 842 deletions(-)
STANDARD DISCLAIMER
-
the exact format of this feature might (will) change before reaching a (GA) release
- it's currently unknown when(if?) it will reach a (GA) release near you
