Forum home » Delegate support and help forum » Microsoft Access Training and help » Foreign key
|
|
|
The UK's most regular Training information: See also: |
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Foreign key
Resolved · Low Priority · Version 2003
Reply Mon 3 Nov 2008, 11:29Delegate Sara said...
For upcoming training course dates see: Pricing & availability
Reply Tue 4 Nov 2008, 22:40Delegate Mark said...
RE: foreign key
Hi Sara,
In general, relationships between tables are such that the relationship between two tables means one table has the 'parent' records and the other has the 'children' records. This is mosty referred to as 'one to many' but I think parent and child makes undertanding struture easier.
If Table A is for CD album titles and Table B is for the tracks titles, then Table A is the parent because each CD album can have any number of tracks (children) from 1 to 30 or more. For the database to work properly each track (child) must know what parent it belongs to ie to what album title it belongs to.
As you could have more than one album with the same title (such as 'Greatest Hits' or 'The Alternate Album') Table A title records (parents) will have the unique identifier of a primary key, which is generally a sequential number against each record given by the access program. This primary key number gives each track a definate reference to claim which album it belongs to.
Each track record in Table B must store a record of which primary key it belongs to. So this is stored in a field in Table B which you might call AlbumID. This field, AlbumID is the foreign key.
So the foreign key is the field in Table B that provides the link information to Table A.
Typically arround 20 different tracks records will all have the same primary key that it is linked to, so may all store the number 7 if they relate to the 7th album entered in table A. The another 10 track may realte to album 8. This means the foreign key value is not unique (an so it is not the primary key of table B or anyting to do with it).
I have attempted to give you a full understanding type of answer. I hope I haven't just confused you too much!
Regards, Mark (delegate).
Reply Tue 4 Nov 2008, 23:33Delegate Mark said...
RE: foreign key
The story continues with my answer to Valeries post!! -Mark
RE: foreign key
Dear Sarah
Thank you for attending Access Introduction course!! I hope you enjoyed the course and benefited from it.
Mark has kindly provided a detailed explanation for which I would like to thank Mark.
More practical approach to primary and foreign keys is explained in Intermediate level where we see Three rules of Normalisation and then also look as diffrent types of relationships that can exist between two or more tables!!
In brief Primary key is a unique identifier for a record so no two records can have the same data in the field that is set to primary key.
In order to link the two tables you may have the field with the same name in the other table but not as a Primary key but as a foreign key.
I hope this can be clear from the example below:
One department can have many employees but one Employee can only be in one dept at one time. So there should be One-to-many relationship between the two tables called Employees and Department.
Employee table will have a primary key called Employeed ID
Department table will have a primary key called Department Code.
But in Employee table you will also have the Department code to specify which employee is in what dept. So the dept. code in the Employees table will be the foriegn key.
If you are familiar with the process of establishing relationship then you may know that in the relationships window you'll drag the Primary key in the departmens table and drop it on the foreign key of the Employees Table i.e. department code (primary key) in the department table to the Deartment code (foreign Key) in the employeses table.
I hope this has answered your query.
If this has answered your query then I would request you to please mark the question as resolved!! If not and you have a specific question related to this then please post it as a new question and we should be able to provide you the solution for it!!
Kindest Regards
Rajeev Rawat
MOS Master Instructor 2000 and 2003
MCAS Master Instructor 2007
|