What is foreign key?
View Single Post
08-09-2007, 11:47 PM
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
. 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
column (again primary key for Sites) along with a
column will always contain an
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
column for a site but no user with that
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
View Public Profile
Visit Salathe's homepage!
Find More Posts by Salathe