mysql-5.6 icon indicating copy to clipboard operation
mysql-5.6 copied to clipboard

Implement Foreign Key Support in MyRocks

Open alxyang opened this issue 7 years ago • 6 comments

This will github issue will track the progress of foreign key support in MyRocks.

Initial proposal/thoughts (credits to @jkedgar):

  1. To handle foreign keys in MyRocks, we need to create a new key type. This new key type will contain the columns of the foreign keys, followed by the columns of the primary key of the referencing table. No data is needed in the value portion. The columns of the foreign key give us access to the primary key, but since they are not unique by themselves (many rows in t2 can reference the same row in t1) we need to add the primary key of the referencing table (t2).
  2. When a row is inserted or updated in the referencing table we need to validate that either the foreign key columns are NULL or the matching row in the referenced table exists. Otherwise we need to fail the insert/update. If the operation is an update and the foreign key columns are not changing we can skip this check.
  3. When a row is updated or deleted in the referenced table we need to check the ON {UPDATE|DELETE} behavior for each referencing foreign key. If the operation is an update and the referenced columns are not changing we can skip this check.
    1. If the specified behavior is RESTRICT and we have rows in the referencing table that match our row, we need to fail the operation.
    2. If the specified behavior is CASCADE and we have rows in the referencing table that match our row, we need to apply this change to those rows. If the operation is a delete then those rows need to get deleted. If the operation is an update, the matching columns in the rows of the referencing table need to get updated. Either way, these operations can fail and if they do, we need to fail the top level operation.
    3. If the specified behavior is SET NULL and we have rows in the referencing table that match our row, we need to update those rows to set the referencing columns to NULL.

alxyang avatar May 03 '17 18:05 alxyang