What would you like to know more about?

Primary & Foreign Keys

Definitions for primary and foreign keys, as well as where to find them in MinistryPlatform.

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

Primary Keys
Each table has a special column that identifies each record with a unique value. In MinistryPlatform, primary keys are integers which automatically increment. These are the first column and are named after the table with "_ID" appended. In the example below, the primary key for Contacts is Contact_ID and the primary key for Contact_Statuses is Contact_Status_ID.
Foreign Keys
A foreign key is a column in a table which refers to the primary key in another (foreign) table.

Contact/Contact Statuses

Here is a 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 store the same value in a foreign key column named Contact_Status_ID to refer to that status. These are commonly named the same in both tables.

The Contacts table has a key icon next to the Contact_ID field, the Contact_Statuses has a key icon next to the Contact_Status_ID field. A black line connects the Contact_Status_ID field in the Contacts table to the Contact_Status_ID field in the Contact_Statuses table. There is an infinity symbol next to the black line next to the Contact_Status_ID field in the Contacts table, and there is a 1 next to the black line next to the Contact_Status_ID field in the Contact_Statuses table

Finding Keys in MinistryPlatform

Advanced Search

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 Advanced Search Tool dialog box. The Gender, Marital Status, Contact Status, Household, and Household Position folders are on the left side. The Marital Status folder is open to display the Marital Status ID and Marital Status fields

Link Navigation

The small link icons represent foreign keys and related tables. Click on these to follow the relationships to pages which represent the related tables. Click the link next to Contact Status: Active to go to the Contact Statuses page, landing on the record "Active".

Jennifer Anderson's Contact record with blue link icons next to the Gender, Contact Status, Marital Status, and Household fields

Drop-Down Lists & Pick-Lists

When you edit a record, drop-down lists and pick-lists represent foreign keys. When you select an item, you store the primary key value of a related table in the foreign key column of the page table.

Jennifer Anderson's Contact record with the Gender drop-down list expanded to display the options for Female and Male