Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Foreign key????
- Thread starter RJ5
- Start date
The easiest way (for me, at least) to understand Foreign Key (FK) is to first understand Primary Key (PK):
A Primary Key is the expression (one or more columns) that will always only one record from the table. For example: a checking account number.
A Foreign Key, then, is a value in a different table that refers to that PK. For example: I have a table that records deposits and checks against a specific account. The account number will appear multiple times in this table, since you'll have multiple deposits and checks. But the account number will always refer to one and only one record in your master Accounts table. Since it always refers to exactly one record in a different ("foreign"
table, it is a Foreign Key. Robert Bradley
Sr. DBA, some big company
cheap prints and oil paintings:
A Primary Key is the expression (one or more columns) that will always only one record from the table. For example: a checking account number.
A Foreign Key, then, is a value in a different table that refers to that PK. For example: I have a table that records deposits and checks against a specific account. The account number will appear multiple times in this table, since you'll have multiple deposits and checks. But the account number will always refer to one and only one record in your master Accounts table. Since it always refers to exactly one record in a different ("foreign"
table, it is a Foreign Key. Robert BradleySr. DBA, some big company
cheap prints and oil paintings:
Bernadette
MIS
Hi There Randy
A foreign Key is a field with is linked to a field in another table (usually a primary key). It helps with database normalisation and reduces repeating data.
Example
-------
Say I have a flat file table of students with the following structure.
ID Class Last First
-------------------------------------
1 1A Smith Tom
2 1A Ryan Mary
3 2A Kelly Brian
4 2A Smith Ann
we usually would split the above table into two tables
Class Table
------------
ClassID Name
------- -----
1 1A
2 2A
3 3A
Student Table
------------
ID ClassID Last First
--- ---- ----- -----
1 1 Smith Tom
2 1 Ryan Mary
3 2 Kelly Brian
4 2 Smith Ann
ClassID which is the Primary Key in the Class Table[/b] is joined to the Students Table by ClassID which is a Foreign Key.
If you notice, now that we have split the file up, we don't have repeating data.(i.e. the class name is not repeated in each record).
Furthermore if I am entering a Class in the Students table, because it is a Foreign key it MUST be present in the Class table before I can enter it. (this is called "Referential Integrity"
I hope this helps with your understanding of "Foreign Keys"!
:->
Bernadette
A foreign Key is a field with is linked to a field in another table (usually a primary key). It helps with database normalisation and reduces repeating data.
Example
-------
Say I have a flat file table of students with the following structure.
ID Class Last First
-------------------------------------
1 1A Smith Tom
2 1A Ryan Mary
3 2A Kelly Brian
4 2A Smith Ann
we usually would split the above table into two tables
Class Table
------------
ClassID Name
------- -----
1 1A
2 2A
3 3A
Student Table
------------
ID ClassID Last First
--- ---- ----- -----
1 1 Smith Tom
2 1 Ryan Mary
3 2 Kelly Brian
4 2 Smith Ann
ClassID which is the Primary Key in the Class Table[/b] is joined to the Students Table by ClassID which is a Foreign Key.
If you notice, now that we have split the file up, we don't have repeating data.(i.e. the class name is not repeated in each record).
Furthermore if I am entering a Class in the Students table, because it is a Foreign key it MUST be present in the Class table before I can enter it. (this is called "Referential Integrity"
I hope this helps with your understanding of "Foreign Keys"!
:->
Bernadette
- Thread starter
- #4
- Status
Similar threads
- Locked
- Question