Foreign Key

Foreign Key fields (listed under Advanced Field Types in the field type dropdown) link a record to a related record in another table by storing that record’s primary key (num or id). The link is enforced by a MySQL foreign key constraint: the referenced record must exist, and you choose what happens when it is deleted. This prevents accidental deletions and orphaned references in a way that a list field populated from a database table does not. The shared options (field label, database field, visibility, system field) work the same as for other field types. See Field Editor.

Field options

Allow Null

Allows blank values, stored as NULL in the database. Checked by default. When unchecked, the column is created as NOT NULL and the record editor requires a selection. Unchecking it has two restrictions: it can’t be combined with ON DELETE “SET NULL”, and it isn’t allowed when adding the field to a table that already has records, since the new column would have to be NULL for those existing records.

Primary Key

The table and primary key column the field references, listed as “table (column)”. Only CMS tables (those using the table prefix) with a primary key appear in the list.

Dropdown Label

A SQL column or expression used as the display label for records in the referenced table, such as name or CONCAT_WS(" - ", id, name). It labels the options in the record editor dropdown and the field’s value on view and list pages. The expression is checked against the referenced table when the field is saved; an invalid column or expression shows an error listing the valid columns.

ON DELETE

What happens when a referenced record is deleted:

• Set this Foreign Key to NULL (SET NULL) — linked records are kept but the field is set to NULL, removing the link. Requires Allow Null.
• Block Deletion (RESTRICT) — the default. Referenced records can’t be deleted while any foreign key links to them.
• Delete This Record (CASCADE) — records linking to the deleted record are deleted along with it.

ON UPDATE

What happens if the primary key value of a referenced record changes: Block Update (RESTRICT), the default, or Update Linked Records (CASCADE), which updates stored foreign key values to match. Record numbers aren’t meant to change, so this is rarely needed.

The Validation section has no options for this field type; whether a value is required is controlled by Allow Null.

In the record editor

The field renders as a searchable dropdown. Options are loaded from the referenced table as the user types or scrolls (50 at a time), labeled and sorted by the Dropdown Label expression. Records whose label expression is blank appear as “#id (no label)”. On record view and list pages the field displays the linked record’s label rather than the stored key.

MySQL column and constraint

The column is created with the same column type as the referenced primary key column, NULL or NOT NULL per Allow Null, and a FOREIGN KEY constraint referencing the selected table and column with the chosen ON DELETE and ON UPDATE actions. MySQL also indexes the column as part of the constraint.

If the column already contains values that don’t exist in the referenced table, saving the field fails with an error listing the invalid values so they can be cleaned up first. Changing the field to another type drops the constraint.

In viewers

The field holds the referenced record’s primary key value, or NULL when blank:

<?php echo $record['brandNum'] ?>

To display data from the linked record, join the referenced table with the leftJoin viewer option:

list($listings, $listingsMetaData) = getRecords([
  'tableName' => 'listings',
  'leftJoin'  => ['brands' => 'brandNum'],
]);

foreach ($listings as $listing) {
  echo $listing['brands.name'];
}

The 'foreignTable' => 'localField' shorthand joins to the foreign table’s num column; when the foreign key references a different column, use a custom ON clause instead, such as 'brands' => 'ON listings.brandNum = brands.id'. Joined columns are returned under qualified keys such as $listing['brands.name'].