foreign key

[yemi atoyebi]

Please can you define in a very simply way with a good
example what "FOREIGN KEY" is in access.

Thank you

Yemi

 

[John Vinson]

Please can you define in a very simply way with a good
example what "FOREIGN KEY" is in access.

It is a field (or a combination of fields) in the "many" side table of
a one to many relationship, which is used as a link to the Primary Key
field (or fields) of the "One" side table.

For example, if you have a contacts database you might have a table of
Companies related one-to-many to a table of Contacts, people at that
company. The primary key of the Companies table might be a CompanyID
autonumber; in the Contacts table you'ld have a Long Integer field
CompanyID (the name can actually be different if you wish) which
indicates which company this person is a part of. This is the "Foreign
Key".

 

[Tim Ferguson]

Please can you define in a very simply way with a good
example what "FOREIGN KEY" is in access.

Imagine a car-hire database. There is a table that contains all the hirings
that have been made:

Person Car OutDate InDate
====== ======= ========== ----------
Eric L123NHY 12/03/2003 15/03/2003
Sammy B920KIY 13/09/2004 27/06/2003
Eric L123NHY 19/03/2003 22/03/2003
Leon M7HHW 21/03/2003 Null

.... and so on.

The Car field contains pointers to the IndexNumber in the Cars table, so
that if you want to find information about the cars that were hired by
Eric, you get the db engine to look up that car:

IndexNum Make Model Year Colour NumSeats
======== ---- -------- ---- ------ --------
L123NHY Ford Mondeo 1999 Puce 4
B920KIY Reno Espace 2001 Grey 34


and so on.

Now, the column Cars.IndexNum is called the Primary Key, because it is the
prime method of identifying each row in the table. The column Hirings.Car
is a Foreign Key that references the PK of the Cars table.

Similarly, the column Hirings.Person is a foreign key that references the
PK of the People table.

Hope that helps


Tim F