Hey everyone!
When is it a good idea to use foreign keys and are they necessary?
Foreign Keys
Moderator: General Moderators
Re: Foreign Keys
I guess I don't understand your question. If you need to refer to a record in another table, the only way I know to do it is by using a foreign key. So it's not a matter of "a good idea". If you need to do that, it's absolutely necessary. If you don't, the concept doesn't even exist.arpowers wrote:When is it a good idea to use foreign keys and are they necessary?
Re: Foreign Keys
I think he means the actual instruction to specify that a Key is foreign, not the concept of a foreign key.califdon wrote:I guess I don't understand your question. If you need to refer to a record in another table, the only way I know to do it is by using a foreign key. So it's not a matter of "a good idea". If you need to do that, it's absolutely necessary. If you don't, the concept doesn't even exist.arpowers wrote:When is it a good idea to use foreign keys and are they necessary?
I have personally found no use for foreign keys in MySQL databases, but they have a very good use in Access databases or SQL Server...arpowers wrote:When is it a good idea to use foreign keys and are they necessary?
Re: Foreign Keys
What's so different about foreign keys in mysql and mssql?emmbec wrote:I have personally found no use for foreign keys in MySQL databases, but they have a very good use in Access databases or SQL Server...arpowers wrote:When is it a good idea to use foreign keys and are they necessary?
Re: Foreign Keys
They can be useful to set up if you have complex tables that have direct relationships. Say you have a list of users and a list of products. You also have a table that assigns products to users.
A pair of foreign keys configured for cascade on delete on this assignment table will mean that if you delete either a user or a product, then all assignment entries referencing the user or product to be deleted are deleted along with it. It will also prevent rows being created for users or products that don't exist.
These things can, of course, be done manually, but sometimes it's just good to know that your data is not littered with 'orphaned' data, without having to hunt them down, or chase queries with follow up queries at process time.
When you don't specifically want these behaviors, then you wouldn't set up a foreign key.
Of course, there are probably many other uses, but these are mostly what I have used them for.
A pair of foreign keys configured for cascade on delete on this assignment table will mean that if you delete either a user or a product, then all assignment entries referencing the user or product to be deleted are deleted along with it. It will also prevent rows being created for users or products that don't exist.
These things can, of course, be done manually, but sometimes it's just good to know that your data is not littered with 'orphaned' data, without having to hunt them down, or chase queries with follow up queries at process time.
When you don't specifically want these behaviors, then you wouldn't set up a foreign key.
Of course, there are probably many other uses, but these are mostly what I have used them for.
Re: Foreign Keys
No man is an island, no man stands alone: Relationship always matters to relate others![]()