Normally, a database contains many tables holding related information. For example, in the Commonwealth Auto database, there is a table storing employee information and a table storing department information. Since each employee is associated with a department, there is a logical relationship between the two tables.
Foreign Keys
The database designer establishes relationships among tables by defining foreign keys. A foreign key is a value or combination of values in a table that exists as the primary key in another table. The names of the columns that make up the foreign key do not have to be the same as the primary key column names.
When you need to retrieve data in two tables at the same time, you use a foreign key and a primary key as common columns (columns that are common between the tables).
Here's an illustration of the relationship between the EMPLOYEE and DEPARTMENT tables:
EMPLOYEE ┌────────┬───────────┬───────────┬─────────┐ │EMP_ID │EMP_LNAME │EMP_FNAME │DEPT_ID │ ├────────┼───────────┼───────────┼─────────┤ │2096 │CARLSON │THOMAS │4600 │ │ │ │ │ │ │2437 │THOMPSON │HENRY │4600 │ │ │ │ │ │ │2598 │JACOBS │MARY │5100 │ └────────┴───────────┴───────────┴─┬───────┘ │ │ │ DEPARTMENT │ ┌─────────┬──────────────────────┐ └──────┤DEPT_ID │DEPT_NAME │ ├─────────┼──────────────────────┤ │5200 │CORPORATE MARKETING │ │ │ │ │4600 │MAINTENANCE │ │ │ │ │5100 │BILLING │ └─────────┴──────────────────────┘
The department ID, DEPT_ID, is the primary key in the DEPARTMENT table and a foreign key in the EMPLOYEE table.
To find the name of the department that an employee is associated with, you would match the two tables based on this common column.
To find the name of the department that employee 2096 is associated with, you would look up the employee in the EMPLOYEE table based on the employee ID, 2096, and find department ID 4600. Then you would find the matching department ID 4600 in the DEPARTMENT table to find the department name, Maintenance.
|
Copyright © 2014 CA.
All rights reserved.
|
|