View Single Post
08-09-2007, 11:47 PM
#3
Salathe is offline Salathe
Salathe's Avatar
Status: Community Archaeologist
Join date: Jul 2004
Location: Scotland
Expertise: Software Development
Software: vim, PHP
 
Posts: 3,820
iTrader: 25 / 100%
 

Salathe will become famous soon enough

Send a message via MSN to Salathe

  Old

In basic terms, a foreign key references a link between one table and another. Lets take an example:
Table Users contains a number of columns, one of which is id. This column is the primary key, a unique marker for each database record (row). Table Sites contains a number of columns including it's own id column (again primary key for Sites) along with a user_id column.

The user_id column will always contain an id from the Users table -- it links a particular site in the Sites table, with a user in the Users table. This particular relationship allows for many sites to be linked to a user (Eg, SELECT * FROM Sites WHERE user_id = 1).

Many database systems (sometimes optionally) enforce what's called foreign key restraints. That means that if we tried to put a value of, for example, 50 in the user_id column for a site but no user with that id existed in the Users table then an error would occur because it's impossible to link a site with a user that doesn't exist!

It can take a little while to get your head around (especially when multiple foreign keys reference many tables!). If my babbling didn't get the idea across, just let me know and I can try again.

It really gets cool when you want to retrieve records from a mix of tables. E.g., SELECT * FROM Sites JOIN Users ON Users.id = Sites.user_id WHERE Users.username = 'Salathe'. The previous query would return my sites, but the Sites table doesn't directly know my username... only my id.