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.

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.

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.

Linking a primary key from one database table to a foreign key in a separate table is a fundamental aspect of database design, ensuring data integrity, consistency, and the establishment of relationships between tables. This process, referred to as creating a foreign key constraint, involves a series of steps that vary depending on the database management system (DBMS) in use. However, the general principles remain consistent across most systems.
Here are the general steps to link a primary key from one table to a foreign key in a separate table:

1. Identify the tables and the relationship: First, determine the two tables you wish to connect and the nature of the relationship between them. The relationship is typically one-to-many or one-to-one, with one table containing a primary key (parent table) and the other table containing a foreign key (child table) referencing the primary key.
2. Ensure primary key constraints: Verify that the parent table has a primary key constraint on the column(s) you intend to reference. A primary key constraint enforces uniqueness and non-null values, ensuring that every row in the table can be uniquely identified.
3. Create the foreign key column: In the child table, create a column or set of columns that will store the foreign key values. The foreign key column(s) must have the same data type(s) as the primary key column(s) in the parent table, and they should ideally have a descriptive name to indicate the relationship.
4. Define the foreign key constraint: In your database management system, use the appropriate SQL command or graphical user interface to create a foreign key constraint between the primary key column(s) in the parent table and the foreign key column(s) in the child table. This process will vary depending on your specific DBMS.
   For example, using SQL syntax in a system like MySQL or PostgreSQL, you would create a foreign key constraint as follows:
   

   ALTER TABLE child_table_name
   ADD CONSTRAINT constraint_name
   FOREIGN KEY (foreign_key_column_name)
   REFERENCES parent_table_name (primary_key_column_name);
   

   
   Replace child_table_name, constraint_name, foreign_key_column_name, parent_table_name, and primary_key_column_name with the appropriate names for your specific tables and columns.
5. Enforce referential integrity: The foreign key constraint enforces referential integrity, meaning that any value entered in the foreign key column(s) of the child table must have a corresponding value in the primary key column(s) of the parent table. This ensures data consistency and helps prevent orphaned records.

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.