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.
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.
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".
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.