Help with Foreign Keys

[eluser]orokusaki[/eluser]
Hi,

I'm wondering if somebody out there could explain a foreign key set up and usage for me. I don't necessarily need code, but just a logical explanation. Here's what I know:

Foreign keys are used to identify primary keys in other tables

You can only use a foreign key in an InnoDB table (at least in MySQL)


Here's what I really don't understand:

When I first learned about database interaction a year ago, I read about foreign keys. It made sense to me then because they explained it as such that it was not an actual difference from any other column, but rather it was just a normal column that you knew was a "foreign key", so that you could say

SELECT 'column' FROM 'table' WHERE table.column = other_table.column

Pardon my FUBAR syntax above.

I'm just so confused. I don't know if I should use foreign keys or not. If I have a table called posts and another called comments should I use foreign keys to relate them to each other, or should I just use the foreign key-like logic in my programming?

[eluser]gon[/eluser]
You should use foreign keys. Not everybody does, but it makes databases more secure and consistent.

What you would get in the post / comments example is:

* you can't add a comment having the id of a non-existant post.
* you can delete a post and have all of it's comments deleted automatically, or prevent a post from being deleted if a comment exists (depending on how you create the key).

You would link the same way posts and users, assuring that every post has a real user assigned, etc.

[eluser]GSV Sleeper Service[/eluser]

You can only use a foreign key in an InnoDB table (at least in MySQL)

not true, you can only use foreign key constraints in an InnoDB table. There's no actual way of knowing that a column is a foreign key in a MyISAM, but you can still set them up as such, it just won't inform you that you are about to destroy the referential integrity of your DB when you try to do something silly (like deleting a comment that is still referenced in your posts table)

[eluser]sl3dg3hamm3r[/eluser]
[quote author="GSV Sleeper Service" date="1225210029"](like deleting a comment that is still referenced in your posts table)[/quote]

you rather mean like deleting a post that is still referenced in your comment's table (post is gone, comments are still there without any meaning)

[eluser]GSV Sleeper Service[/eluser]
ah yes, that's what I meant.

[eluser]bigtimslim[/eluser]
Thanks, these posts are helpful to me. I design all of my tables with relationships even if I can't really define them, but have always wondered.

[eluser]orokusaki[/eluser]
Thanks a lot for all the great replies guys. I love the community here. I do have one more question though. I know I said no code was needed, and it's really not still, but I am still a little confused about the implementation of foreign keys.

How do you use them. Do they work automatically. Do two tables that reference each other delete columns at the same time like a DB transaction, or do you still have to do the programmatic legwork, and they just prevent you from completing the transaction unless all connected data is deleted. Do you have to use JOIN?

By the way, is JOIN the sort-of other way of doing foreign key databases without using foreign keys.

[eluser]sl3dg3hamm3r[/eluser]
The different types of constraints you can use are pretty well explained here.

As said before, these rules help you to ensure data-integrity WITHOUT programming any logic/checks.

Again an example: A small shop with two tables 'customer' and 'boughtproduct' - boughtproduct contains all products customers have ever bought. This means it also has a foreign key referencing the customer-table.

Now you could define a 'delete cascade' - constraint. If you delete the customer, all its related products are also deleted, transaction-safe. You don't need to bother in your code about this.

Another contraint: 'delete restrict'. IF there are products with the customer connected, the db will return an error if you try to delete the customer. There you would need to code a little bit, since you should check before deleting a customer if there are products. Otherwise the user might face an ugly error from the db (which anyhow shouldn't be shown for safety-reasons).

There are more constraints described if you follow the link. It is all about data-integrity, which can be provided by the database itself, without writing any code. This makes it save, because this kind of checks you might forget quite easily while developing your application.

Hope this cleared it up a little bit...
Sl3dg3