Implement Foreign Key Support in MyRocks

[alxyang]

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.

[otherNoscript]

Hello. I wonder when foreign keys will be implemented?

PS Sorry for my English.

[poige]

Well, typically when you use any mature RDBMS like Postgresql, ORACLE, DB2 you just use it w/o hanging around some half-complete storage engines ;)

[nilroe]

it's this in the roadmap?

[YouveGotMeowxy]

Bueller?

[hermanlee]

The feature is not on our roadmap since it is not used by most of our applications.

[YouveGotMeowxy]

The feature is not on our roadmap since it is not used by most of our applications.

Ahh, damn. I was excited to try using RocksDB after reading it's features (by converting some existing innoDB db's to RocksDB). So then Rocks simply is unusable if you want to link tables to other tables? (foreign keys).