Database Design   «Prev  Next»

Lesson 5 Linking relational database tables
ObjectiveLink a primary key to a foreign key in a relational database system

Linking Relational Database Tables using Primary and Foreign Keys

The power of relational databases stems from the fact that tables can be linked, enabling users to gain access to data across tables. Let us briefly examine how links are formed between tables.

Linking Tables

Two tables are linked when they have a matching field, that is, a field in each table containing similar data. For example, suppose you want to link a Suppliers table with another table called Products.
Both tables must have at least one field in common. If you insert the primary key field of the Suppliers table (for example, SuppID) into the Products table, the two tables will have a matching field: SuppID.
The tables are now linked, and you can access their data together.

Accessing Data

A user can now ask the database to retrieve data that spans both tables.
A typical supplier-product query might be:
What's the name of the company that supplies us with product X?
The graphic below illustrates the two linked tables in which the data that answers this question resides, and the results of the query.

SuppID is the primary key in the Suppliers table and a foreign key in the Products table.
What is the name of the company that supplies us with the Beta tool?
SuppID is the primary key in the Suppliers table and a foreign key in the Products table.

Linking a Primary Key from one Database Table to foreign key in a Separate Table

Data that is organized in a useful way is called information.
The data returned from the supplier-product query is an example of organized data (information).
You will learn a great deal more about linking tables in later modules. It is important to remember that matching fields between tables serve to link tables.
The next lesson introduces you to Structured Query Language (SQL), the programming language used to create relational database tables.