Page 1 of 1

Foreign Keys

Posted: Fri Dec 07, 2007 6:43 pm
by arpowers
Hey everyone!

When is it a good idea to use foreign keys and are they necessary?

Posted: Sat Dec 08, 2007 7:41 am
by feyd
If tables relate, a foreign key is often useful. When they don't relate, there's no reason to use one. Pretty straight forward.

They aren't absolutely necessary, but it makes the job of relating two or more tables very tough if they aren't there.

Re: Foreign Keys

Posted: Sat Dec 08, 2007 2:11 pm
by califdon
arpowers wrote:When is it a good idea to use foreign keys and are they necessary?
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.

Re: Foreign Keys

Posted: Mon Jan 14, 2008 5:22 pm
by emmbec
califdon wrote:
arpowers wrote:When is it a good idea to use foreign keys and are they necessary?
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.
I think he means the actual instruction to specify that a Key is foreign, not the concept of a foreign key.

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...

Re: Foreign Keys

Posted: Mon Jan 14, 2008 6:12 pm
by Weirdan
emmbec wrote:
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...
What's so different about foreign keys in mysql and mssql?

Re: Foreign Keys

Posted: Mon Jan 14, 2008 6:46 pm
by Stryks
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.

Re: Foreign Keys

Posted: Wed Jan 23, 2008 3:57 am
by junix
No man is an island, no man stands alone: Relationship always matters to relate others :banghead:
:drunk: