Today's Posts Follow Us On Twitter! TFL Members on Twitter  
Forum search: Advanced Search  
Navigation
Marketplace
  Members Login:
Lost password?
  Forum Statistics:
Forum Members: 24,254
Total Threads: 80,792
Total Posts: 566,471
There are 1148 users currently browsing (tf).
 
  Our Partners:
 
  TalkFreelance     Design and Development     Programming     .NET and MSSQL :

What is foreign key?

Thread title: What is foreign key?
Closed Thread    
    Thread tools Search this thread Display Modes  
08-09-2007, 10:46 PM
#1
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  What is foreign key?

I've searched on google and haven't found any easy to understand article about what is a foreign key. What's the use of foreign keys?

Can anyone please help me out?

08-09-2007, 10:56 PM
#2
CreativeLogic is offline CreativeLogic
CreativeLogic's Avatar
Status: Request a custom title
Join date: Feb 2005
Location:
Expertise:
Software:
 
Posts: 1,078
iTrader: 6 / 100%
 

CreativeLogic is on a distinguished road

Send a message via MSN to CreativeLogic

  Old

Is this supposed to be in the MySQL forum?
http://dev.mysql.com/doc/refman/5.1/...eign-keys.html

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.

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?

12-02-2007, 06:04 PM
#5
zhaoyun is offline zhaoyun
zhaoyun's Avatar
Status: Junior Member
Join date: Mar 2005
Location:
Expertise:
Software:
 
Posts: 28
iTrader: 1 / 100%
 

zhaoyun is on a distinguished road

Send a message via MSN to zhaoyun

  Old

Yes, you'll have to use the foreign key to relate the three tables together.

12-18-2007, 10:37 PM
#6
Luk3 is offline Luk3
Luk3's Avatar
Status: Senior Member
Join date: Nov 2006
Location: Colchester, UK
Expertise:
Software:
 
Posts: 760
iTrader: 3 / 100%
 

Luk3 is on a distinguished road

Send a message via MSN to Luk3

  Old

Yeah usually the foreign key is the primary key from another table.

Say you have a customer table and the headers are:
Customer ID [Primary]
Customer Name
ETC

and then you have an Orders table:
Order ID [Primary]
Order item
Customer ID [Foreign]

The tables link via the foreign key. Creates a relationship, Referential Intergrity FTW! ;D

Closed Thread    


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

  Posting Rules  
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump:
 
  Contains New Posts Forum Contains New Posts   Contains No New Posts Forum Contains No New Posts   A Closed Forum Forum is Closed