1.2 Table Relationships & Cascading Rules

Table Relationships & Cascading Rules

Relational integrity is the bedrock of enterprise application architecture. In Microsoft Dataverse, relationships define how records in one table link to records in another. Beyond simple foreign key constraints, Dataverse relationships incorporate sophisticated cascading behaviors that automatically propagate security, assignment, reparenting, and deletion operations down complex parent-child hierarchies. Mastering these behaviors is vital for passing the PL-200 exam and preventing unintended data loss or security exposure in production.

---

1. Relationship Types in Dataverse

Dataverse supports three core relationship structures:

+-----------------------------------------------------------------------------+
|                        DATAVERSE RELATIONSHIP TYPES                         |
|                                                                             |
|   [1:N / N:1 RELATIONSHIP]                                                  |
|   Account (1) ------------< (N) Contact                                     |
|   - Foreign key lookup column created on the Child (N) table                |
|                                                                             |
|   [NATIVE N:N RELATIONSHIP]                                                 |
|   Student (N) >--- [Hidden Intersect: student_course] ---< (N) Course       |
|   - System-managed table, stores only StudentID + CourseID                  |
|                                                                             |
|   [MANUAL INTERSECT TABLE (JUNCTION)]                                       |
|   Student (1) --< (N) Enrollment (N) >-- (1) Course                         |
|   - Custom table with metadata: Grade, Term, Status, PaidDate               |
+-----------------------------------------------------------------------------+

1. One-to-Many (1:N) and Many-to-One (N:1)

• A 1:N relationship represents a primary parent table linked to multiple child records (e.g., one Account has many Contacts).
• An N:1 relationship is simply the reciprocal view from the child table looking back to its single parent.
• Mechanism: Dataverse creates a Lookup column on the child table that stores the 128-bit GUID of the parent record.

2. Native Many-to-Many (N:N)

• Links multiple records in Entity A to multiple records in Entity B (e.g., Events and Attendees).
• Hidden Intersect Table: Dataverse automatically provisions and manages a hidden physical intersect table in the database. This table contains only two columns: the Primary Key GUID of Table A and the Primary Key GUID of Table B.
• Limitations: Users cannot add custom columns, forms, views, or business logic to a native N:N intersect table.

3. Manual Intersect (Junction) Table Pattern

When business requirements dictate that metadata must be captured about the relationship itself, consultants must implement a Manual Intersect Table (also known as a junction or bridge table).

• Structure: A custom table is created that sits between the two primary entities. The junction table contains two separate N:1 lookup columns (one pointing to Table A, one pointing to Table B).
• Metadata Payloads: The junction table can contain custom columns such as EnrollmentDate, FinalGrade, Role, CertificationStatus, or AssignedBy.

---

2. Relationship Behaviors

When configuring a 1:N relationship, the consultant must define the Type of Behavior. This setting controls how operational events performed on the parent record propagate to related child records.

+-----------------------------------------------------------------------------+
|                        RELATIONSHIP BEHAVIOR TYPES                          |
|                                                                             |
|   +-------------------+  +-------------------+  +-------------------+      |
|   |     PARENTAL      |  |    REFERENTIAL    |  | REFERENTIAL (REST)|      |
|   | - Deep cascade    |  | - Independent     |  | - Restrict Delete |      |
|   | - Delete parent   |  | - Delete parent   |  | - Blocks parent   |      |
|   |   deletes children|  |   leaves children |  |   deletion if     |      |
|   | - 1 parent limit  |  | - Link removed    |  |   children exist  |      |
|   +-------------------+  +-------------------+  +-------------------+      |
|                                    |                                        |
|                                    v                                        |
|                          +-------------------+                              |
|                          |   CONFIGURABLE    |                              |
|                          | - Granular tuning |                              |
|                          | - Custom action   |                              |
|                          |   by action rules |                              |
|                          +-------------------+                              |
+-----------------------------------------------------------------------------+

1. Parental

In a Parental relationship, the child record's lifecycle is completely bound to the parent record. Any action taken on the parent record (Assign, Delete, Share, Unshare, Reparent) automatically cascades to all related child records.

• Strict Constraint: A table can have only ONE Parental relationship where it acts as the child entity. A table cannot have two distinct parental parents (e.g., if an Order Item is parented by Order, it cannot also be parented by Product).

2. Referential

In a Referential relationship, records are linked for navigation and lookup purposes, but each record maintains an independent lifecycle.

• If the parent record is deleted, the relationship link on the child record is cleared (Remove Link), but the child record remains active in the database.

3. Referential, Restrict Delete

In this behavior, the system prevents the parent record from being deleted as long as any related child records exist.

• Enterprise Use Case: Preventing a Customer Account from being deleted if open Invoices or active Contracts exist.

4. Configurable Cascading

Allows the functional consultant to customize each cascading action individually to meet precise business rules.

---

3. The 7 Cascading Actions and Option Rules

Dataverse evaluates cascading rules across 7 distinct operations executed on a parent record:

+-----------------------------------------------------------------------------+
|                     THE 7 DATAVERSE CASCADING ACTIONS                       |
|                                                                             |
|   1. ASSIGN      ---> Reassigning owner of parent propagates to children.   |
|   2. SHARE       ---> Sharing parent record grants access to children.      |
|   3. UNSHARE     ---> Revoking share on parent revokes access to children.  |
|   4. REPARENT    ---> Changing parent lookup updates child security scope.  |
|   5. DELETE      ---> Deleting parent cascades, removes link, or restricts. |
|   6. MERGE       ---> Merging master/subordinate parent combines children.  |
|   7. ROLLUP VIEW ---> Surfacing child activities in parent timeline grid.   |
+-----------------------------------------------------------------------------+

Cascading Option Matrix

[!WARNING] Critical Cascading Validation Rules for PL-200:

1. Delete Operation Restrictions: The Delete action supports only three options: Cascade All, Remove Link, or Restrict. Setting Delete to Cascade Active or Cascade User-Owned is invalid and rejected by the platform.
2. Reparent / Assign / Share Restrictions: Remove Link and Restrict Delete are ONLY valid for the Delete action. They cannot be applied to Assign, Share, Unshare, or Reparent.
3. Rollup View Options: Rollup View supports only Cascade All or Cascade None.

Practical Scenario: Cascading Assign vs. Ownership Exposure

Consider a sales organization where an Account is assigned from Sales Rep A to Sales Rep B:

• If the Account-to-Opportunity relationship has Assign = Cascade All, all Opportunities (including closed/lost deals owned by other team members) are reassigned to Rep B.
• If configured as Assign = Cascade User-Owned, only Opportunities currently owned by Rep A transfer to Rep B. Opportunities owned by technical specialists or secondary reps remain untouched.
• If configured as Assign = Cascade None, no Opportunities transfer ownership.