I've never really used foreign keys before but I am building a project where they could come in handy. I have looked at some other large software such as some forum systems and they don't use foreign keys, so I'm wondering why wouldn't they!?
I understand that it would come in handy for situations where they have the username in the
users table and then on the
posts table for example they have the users
username in the author field rather than an id (my assumption is they do this to avoid expensive joins to the users table); so for this situation you would think adding a
foreign key to the
posts table to reference the username would be a good idea.
I'm wondering - when you do something such as this:
// Start transaction SELECT * FROM users WHERE username='some_username' LIMIT FOR UPDATE; // End transaction
Because of the
foreign key placed on the
posts table with reference to the
username in the
users table, does that then cause not only that row in the
users table to be locked but also all rows in the
posts table that reference the users
username to be locked as well?
If not, are there any other reasons why one would not use foreign keys for instances such as this?