How does Drupal not rely on Foreign Indexes for matching table row information?

Question

I am so confused right now in creating a custom table within Drupal 7. My db schema builds a table with three fields one of which is a uid field. I don't have much experience with databases and I was confused as to how to match, relationally, the uid field of my table with the users->uid field. I am told that the foreign key operation within the D7 appear to do this but that they are only there as documentation, the foreign key operation doesn't actually do anything. Is there a way, within the D7 schema, to link two table row items such as uid -- if so how?

Answer 1

Due to mostly historical but also database portability reasons does Drupal currently not use foreign keys.

That however doesn't stop you in any way to relate to other tables. All you need to do is insert the user id into your table and then to a JOIN to the users table if you want to query it. Or do a user_load_multiple() on the uid's separately. If you have a large amount of data, that might actually be better for performance (The default node list in Drupal does that because a LEFT JOIN can get very slow if you have millions of nodes).

You would have to do this in any way, foreign keys don't do this automatically. They do validate your data (e.g. you can't insert a non-existing uid) and you can define how to act when users are deleted (either prevent delete or delete the referenced records too, basically). In the case of users, this wouldn't help you anyway, due to the advanced and configurable User cancellation that Drupal does (e.g. you can configure it to re-assign all public data like comments to the anonymous user).