Multitable support: child tables and junction tables

Last modified 14 Sep 2026 07:59 UTC
EXPERIMENTAL
This feature is experimental. It means that it is not intended for production use. The feature is not finished. It is not stable. The implementation may contain bugs, the configuration may change at any moment without any warning and it may not work at all. Use at your own risk. This feature is not covered by midPoint support. In case that you are interested in supporting development of this feature, please consider purchasing midPoint Platform subscription.
Since 4.11
This functionality is available since version 4.11.
Table of Contents

The SQL framework detects relationships between tables automatically and exposes them as attributes on the ConnId object class.

This article is part of the SQL connector development reference and guidance materials. See How to develop connectors using the SQL framework for the section introduction.

Relationship detection is convention- and metadata-driven; there is no script API to define custom join paths today. The relationship {} function in currently unsupported scripts is a stub, and joinOn / foreignKey blocks (as seen in some example scripts) are illustrative only — do not depend on them.

Relationship detection methods

Before tables are translated to object classes, the framework looks for foreign-key relationships from each table (the "child") to other tables (the "parent"/"target"):

  • Constraint-based — columns that have an actual foreign key constraint to a discovered table.

  • Convention-based — a column named <name>_id where a table (or object class) named <name> exists; the referenced column is assumed to be id.

Depending on what the foreign key columns contribute to the child table’s primary key, the relationship is classified as follows:

Relationship Condition Exposed as

Single-valued embedded

Foreign key columns are the only primary key columns of the child table (child has at most one row per parent)

A single-valued complex attribute (embedded object class value) on the parent, named after the child table

Multi-valued embedded

Foreign key columns are part of the child primary key, but the child also has its own data columns

A multi-valued complex attribute on the parent, named after the child table

Multi-valued simple attribute

Foreign key columns plus exactly one additional non-foreign key column in the child primary key

A multi-valued scalar attribute (of the value column’s type) on the parent

Junction table (many-to-many)

Foreign key columns reference two or more distinct target tables, and the foreign key columns exactly match the child primary key (a junction has no data of its own)

A multi-valued reference attribute on the parent pointing at the target object class

A child table whose primary key contains a surrogate key of its own (e.g., id, user_id) — i.e., the foreign keys are not the primary key — is treated as a standalone table, not a relationship. Detected child/embedded object classes are folded into the parent and are not exposed as separately manageable object classes (no create/update/delete handlers; the data is managed through the parent object class, see Writing to child and junction tables).

Example

Consider this schema:

CREATE TABLE users (id INT PRIMARY KEY, user_name VARCHAR(255), ...);
CREATE TABLE user_emails (user_id INT, email VARCHAR(255),
                          PRIMARY KEY (user_id, email));
CREATE TABLE user_phones (user_id INT, phone_number VARCHAR(50), phone_type VARCHAR(20),
                          PRIMARY KEY (user_id, phone_number));
CREATE TABLE groups (id INT PRIMARY KEY, group_name VARCHAR(255), ...);
CREATE TABLE user_group_membership (user_id INT, group_id INT,
                                    PRIMARY KEY (user_id, group_id));

The discovery produces:

  • users with a multi-valued attribute user_emails (simple attribute: value of the email column) and a multi-valued complex attribute user_phones (embedded rows with phone_number and phone_type)

  • users with a multi-valued reference to groups resolved through the user_group_membership junction table

  • user_group_membership itself is not a standalone object class

The relationship values are resolved at search time in a batch: after the parent rows are selected, the framework issues one query per relationship over the parent UIDs (WHERE <join_column> IN (…​)) and attaches the results.

Behavior

  • Multi-valued attributes and references are populated during search; they are not part of the parent row’s columns.

  • Filtering through a relationship is not part of the built-in filter translation — filter on the parent object class attributes (see SQL search and filter support and Custom search implementation for custom needs).

  • Child-table data is readable and writable through the parent object class — create, update and delete handle the related rows in the same transaction (see Writing to child and junction tables).

  • Read-only flat projections of additional tables (LEFT JOINs) are not relationships — configure them with the join block (see joined attributes (read-only)).

  • Incremental sync of related data is not wired yet.

Guidelines for your database schema

To get relationships detected reliably:

  1. Use real foreign key constraints where possible.

  2. For convention-based detection, name the foreign key column exactly <table>_id and use an id column in the referenced table.

  3. Junction tables must consist only of the two foreign key columns (which form the primary key) — any extra column or a surrogate primary key turns the table into a standalone table.

Was this page helpful?
YES NO
Thanks for your feedback