Foreign Keys ?

[Yusogga]

Could anyone tell me what is the real benefit of using a foreign key?

Let's say I have 2 tables, one with references to categories.

my_categories
id | name
1 | sun
2 | rain
3 | wind

my_info
id | name | cid
1 | Mt. Doom | 2
2 | Dodo Hills | 2
3 | Balaba desert | 3
4 | Tilula plains | 1


I can join two tables using

SELECT A.*, B.name as category FROM my_table A LEFT OUTER JOIN my_ref_table B ON (A.cid = B.id)

resulting in this display

1 | Mt. Doom | rain
2 | Dodo Hills | rain
3 | Balaba desert | wind
4 | Tilula plains | sun

In this case, where does the use of a foreign key would improve a search?

[Kiwi]

Foreign keys are indexed, which makes a search on them faster. I don't know if it's faster than simply indexing the field without defining it as a foreign key or not.

Most of that is secondary. The main point of foreign keys is not speed, but ensuring data integrity and storage efficiency (by eliminating duplication and enforcing the relationships between tables).

[Yusogga]

Yes this is true,
but in the example above, I don't use any foreign key, and I also prevent repetition in my table.

That is this concept that stays obscure to me....

[raf]

Re: Foreign Keys ?

Originally posted by Yusogga
In this case, where does the use of a foreign key would improve a search?
----------------------------------
Yes this is true,
but in the example above, I don't use any foreign key, and I also prevent repetition in my table.

That is this concept that stays obscure to me....

It doesn't improve the search as compaired to a normal index.

Using foreign key has nothing to do with 'preventing repitition' or 'eliminating duplication', because foreign key will typically NOT be unique.
---> the primary key will be unique. Has to be. So inside table 1, you only have 1 record with that PK value (like in my_categorie.id)
---> the foreign key values will typically be used in more then one record inside table2. (like the my_info.cid)
The primary key/foreign key relation is often called a 1-n relation --> one value of the foreign key (inside table 1) is matched with several FK values inside table2 .
This has nothing to do with preventing duplicated values inside your recordset either, because even if you would have an n-m relationship, then there are db's that optimise the recordset so that you don't get duplicates.

The relationship normally defines how the tables will be joined, even if you don't specify how. Like MsAccess implements this --> if you just specify the fields and tables, then MsAcess use the defined relationship to generate the join.
But Kiwi is right about their main function --> ensuring dataintegrity. For instance by enforing cascades (for instance deleting of related records in other tables when the record from the primary table is deleted), by preventing that you delete a column that is used in an existing relation, by not allowing FK value that don't exist in the PK-index etc I don't know th mySQL support for all these functionality, but as far as my info is up to date : mySQL doesn't have full PK-FK relationship support.

But at runtime, when you ecxecute a query, it makes no difference if it is a regular index or a foreign index.
Well, it will if you don't have a PK and FK index and have Null values in the joined fields --> FK and PK will prevent that you have Null values so your always certain that no records were eliminated from the recordset because the joined fields were Null.

So there are 3 main concepts here:
- indexing --> regular indexes, full-text indexes.
- unique indexing --> wether or not to allow duplicate values
- relationship --> a 'fixed' join between two indexed fields

a PK normally is : unique, not Null
a FK normally is : not unique, not Null