Foreign Keys

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
arpowers
Forum Commoner
Posts: 76
Joined: Sun Oct 14, 2007 10:05 pm
Location: san diego, ca

Foreign Keys

Post by arpowers »

Hey everyone!

When is it a good idea to use foreign keys and are they necessary?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Foreign Keys

Post 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.
User avatar
emmbec
Forum Contributor
Posts: 112
Joined: Thu Sep 21, 2006 12:19 pm
Location: Queretaro, Mexico

Re: Foreign Keys

Post 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...
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: Foreign Keys

Post 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?
User avatar
Stryks
Forum Regular
Posts: 746
Joined: Wed Jan 14, 2004 5:06 pm

Re: Foreign Keys

Post 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.
User avatar
junix
Forum Newbie
Posts: 5
Joined: Tue Sep 25, 2007 2:10 am
Location: MSU, Philippines
Contact:

Re: Foreign Keys

Post by junix »

No man is an island, no man stands alone: Relationship always matters to relate others :banghead:
:drunk:
Post Reply