Understanding Foreign Keys

Within your database, various tables may be related to each other. The following shows a typical relationship between tables:

In this example:

• cust_code in Order_Details gives the customer who placed the order. cust_code must match one of the values in the Customer_Details table (cust_code is the Primary Key of Customer_Details).
• cust_code is called a Foreign Key in the Order table. This is because the values of cust_code match the key of another table.
• within AQT, we say that Customer_Details is a Referenced Table of Order_Details.
• Customer_Details is often called a Parent Table of Order_Details. However this can be confusing terminology, as a table can have a large number of parents, which can seem odd.
• For each row in the Order_Details table, there will only be a single matching row in the Customer_Details table (an order can only have one customer). This relationship is therefore a many-to-one relationship.

Looking at the relationship the other way:

• Order_Details is a Dependent Table or Child Table of Customer_Details
• The relationship from Customer_Details to Order_Details is one-to-many (for a given customer, there can be multiple orders).

Defining a Foreign Key Relationship

Most databases allow you to define foreign key relationship. You can do this within AQT by clicking on the table then (on the Admin Toolbar), clicking on the Create (left-most) icon and selecting Foreign Key.

You can also create relationships by defining User-Defined Relationships. When you define a relationship, you need to specify whether it is a Foreign Key relationship, eg. whether the To Table is a Referenced Table or Dependent table of the From Table. To help you determine this:

• if the To Cols are the primary key of the To Table, then it is a FK relationship.
• If the From Cols are the primary key of the From Table then it is not a FK relationship.
• Consider whether it is a one-to-many, or many-to-one relationship. Given one row of the From Table, do you expect there to be just one row in the To Table (many-to-one), or many rows in the To Table (one-to-many)? If it is a many-to-one relationship, then it is a FK relationship.