When records in two or more tables are related, key columns in each table are used to represent the relationship.

Each table has a special column which serves to identify each record by using a unique value. In MinistryPlatform, Primary Keys are implemented as integers which automatically increment. These are the first column and are named after the table with "_ID" appended.

 

In the Contacts/Contact Statuses example below, the Primary Key for Contacts is Contact_ID and the Primary Key for Contact_Statuses is Contact_Status_ID.

A Foreign Key is a column in a table which refers to the Primary Key in another (foreign) table.

Here is simple example of two related records. If Contact_Statuses stores a value of 1 for "Active" status in the Primary Key column Contact_Status_ID, then a Contact record can refer to that status by storing the same value in a Foreign Key column named Contact_Status_ID. These are commonly named the same in both tables.

 

There is much evidence of these Primary/Foreign Key relationships in the platform. Here are three:

The expandable folders in Advanced Search represent Foreign Keys. When you select these, the platform adds a JOIN to your query to get fields from related tables. The Advanced Search uses the Table Lookup Convention to represent table joins.

 

The small link icons represent Foreign Keys and related tables. Clicking on these will follow the relationships to pages which represent the related tables. Clicking the link next to Contact Status: Active will navigate to the Contact Statuses page, landing on the record "Active."

 

When editing a record, drop-downs and pick-lists represent Foreign Keys. When you select an item, you are storing the Primary Key value of a related table in the Foreign Key Column of the page table.