2.3 Associations & Delete Behavior

2.3 Associations & Delete Behavior

In Mendix domain models, Associations establish relationships between entities, governing data navigation, foreign key schema generation, security permissions, and referential integrity. Proper association design ensures relational integrity and optimal query execution, while understanding Delete Behavior prevents data corruption, accidental mass deletions, and orphaned child records in enterprise databases.

---

Association Multiplicity & Physical Database Schema Mapping

Studio Pro exposes three multiplicities on the association properties dialog, and each one fixes the underlying association type and its ownership:

1. One-to-One (1-1)

• Exactly one instance of Entity A is associated with at most one instance of Entity B.
• Ownership: One-to-one is the "owner Both" configuration — both entities refer to each other. There is no separate dropdown for picking a single owning side.

2. One-to-Many (1-*)

• One instance of the entity on the 1 side is associated with zero, one, or many instances of the entity on the * side.
• Ownership: The entity on the * side is the owner (owner Default). The reference member therefore lives on the many side — Order carries Order_Customer, not the other way round.

3. Many-to-Many (*-*)

• Multiple instances of Entity A relate to multiple instances of Entity B; internally this is a Reference set.
• Ownership: Set through Navigability, which offers exactly two choices: X objects refer to Y objects (default — X is the owner) or X and Y objects refer to each other (both are owners).

Read the direction, not the picture. Ownership only controls who may add or change the association. Making one entity the owner never prevents you from reading the association from the other end, and XPath traverses it in both directions.

---

Association Storage: Association Tables vs. Direct Associations

How an association is physically stored is a separate setting from multiplicity, and it is a favourite exam distractor because Mendix has two models:

• New apps created on recent Studio Pro versions implement one-to-many and one-to-one associations as direct associations.
• Apps upgraded from older Mendix versions keep creating association tables for new associations. You switch individual associations over on the association properties dialog, and the choice is reversible.
• Direct associations are not available for view entities, non-persistable entities, or external entities (with the exception of persistable external entities). Mendix also advises against using them inside modules designed to be imported into other apps, such as Marketplace modules, because that can cause unexpected migrations.
• XPath and OQL are identical either way: [Sales.Order_Customer/Sales.Customer/City = 'London'] compiles correctly against both storage models. The difference is join count and write cost, not query syntax.

---

Association Ownership: Access Control & Client Manipulation

Association Ownership is not merely a database technicality; it directly governs Mendix Entity Access security rules and UI permissions:

1. Security Rule Granting: To allow an end user to set, change, or clear an association via a reference selector, input widget, or client-side nanoflow, the user's module role must have Read/Write access to that specific association member.
2. Where Access is Defined: Access rights to an association can only be configured in the Access Rules tab of the Owner entity. If Order owns the association to Customer, permissions to associate a customer to an order must be granted on Order. If an association is owned by "Both" (available in 1-1 and -), access rules can be configured on either entity.

---

The Three On-Delete Behaviors in Studio Pro

When an object is deleted, its link to the counterpart object must be resolved. Open the association (double-click it in the domain model, or use the association tab of the entity properties) and Studio Pro offers exactly three on-delete behaviors — and it offers them separately for each end of the association, so one association carries two independent settings.

Mendix words each option with the real entity names substituted, which is exactly how the exam quotes them:

1. On delete of '{Entity}' object, keep '{Associated Entity}' object(s) — default

• Mechanics: When the object is deleted, the associated object (or objects) are not deleted; only the link between them disappears.
• Risk: Creates orphaned records when the associated entity has no business meaning without its counterpart — for example, leaving OrderLine rows behind whose Order no longer exists.

2. On delete of '{Entity}' object, delete '{Associated Entity}' object(s) as well

• Mechanics: Deleting the object also deletes every associated object, inside the same database transaction.
• Transitive cascades: If the associated entity also cascades on its own associations, the delete propagates down the whole chain (Order → OrderLine → OrderLineDiscount).
• Typical use: True composition, such as deleting a Customer together with its Profile.

3. On delete of '{Entity}' object, delete '{Entity}' object only if it is not associated with '{Associated Entity}' object(s)

• Mechanics: This is delete prevention. The object can only be deleted when it is not associated with any object on the other end.
• Error message: Studio Pro asks you to supply an Error message if '{Entity}' object cannot be deleted, which the runtime shows to the end user when the delete is blocked.
• Typical use: Refusing to delete a Customer that still has Order records.

Exam Trap: There is no fourth "delete the child only if no other parent uses it" setting. Candidates invent one because the third option's long wording sounds conditional — but it prevents the delete, it never performs a selective child delete.

On-Delete Behavior Configuration Matrix

---

Referential Integrity & Preventing Orphaned Records

An orphaned record is a child entity that remains in the database with an empty foreign key, disconnected from its required context. For example, an InvoiceLine without an Invoice corrupts financial reporting and causes NullPointerExceptions in microflows that assume $InvoiceLine/Billing.InvoiceLine_Invoice will always resolve to an object.

To enforce strict referential integrity:

• If associated records cannot logically exist without their counterpart, select delete … object(s) as well.
• If a record represents critical historical master data, configure delete … only if it is not associated with … object(s) on that end so operators cannot delete it by accident.

---

Production Hazards: The Runaway Cascading Delete

While cascading delete seems convenient, applying it carelessly across complex domain models is one of the leading causes of production outages in enterprise Mendix deployments:

The Scenario

Imagine an enterprise ERP where Company cascades to Department, which cascades to Employee, which cascades to Timesheet, which cascades to TimesheetLine. An administrator navigates to a administration screen and clicks "Delete" on an inactive subsidiary Company.

The Catastrophe

1. Mass Deletion: The runtime attempts to delete the company, 20 departments, 800 employees, 250,000 timesheets, and 1.5 million timesheet lines in a single database transaction.
2. Lock Escalation: The database engine escalates row-level locks to exclusive table-level locks on the Timesheet and Employee tables.
3. Application Freeze: All other active users attempting to submit timesheets or update employee records are blocked, queuing up database connections until the connection pool is exhausted.
4. Transaction Timeout: After 60 seconds, the query times out, the entire transaction rolls back, and the server crashes from memory exhaustion.

Architectural Standard: For high-volume transactional entities, avoid automated cascading deletes across deep hierarchies. Instead, write a dedicated, batch-processed background microflow that queries child records in controlled chunks (e.g., 500 at a time), performs audit logging, and commits deletions incrementally.

---

Delete Behavior vs. Before-Delete Microflow Event Handlers

Developers often ask whether to use Studio Pro's built-in Delete Behavior or a Before Delete microflow event handler.

• Studio Pro Delete Behavior: Executed at the platform engine level. It is highly optimized, runs before custom logic, and reliably enforces integrity across all pages, web services, and standard delete actions.
• Before Delete Event Handler: Best used when you need complex business validations (e.g., checking an external SAP system via REST before allowing an order to be deleted, or writing custom historical audit records to a separate compliance table). If a Before Delete microflow returns false, the Mendix runtime halts the deletion and aborts the transaction.