View Single Post
08-10-2007, 08:13 AM
#4
Haris is offline Haris
Status: Request a custom title
Join date: Dec 2005
Location:
Expertise:
Software:
 
Posts: 2,741
iTrader: 9 / 100%
 

Haris is on a distinguished road

  Old

Originally Posted by Salathe View Post
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.
So for example, if I have a quiz table, questions table and candidates table.

All I have to do is link them together through foreign key to retrieve questions and candidates in the quiz table or am I still wrong?