You can use a filtered unique index to make an optional one-to-one association unique in the database; this page is for MDriven Designer modelers who need to prevent several objects from referring to the same object through a single link.

Model a one-to-one association with one embedded end

A single link is an association end whose multiplicity allows at most one related object. In MDriven Designer, a one-to-one association is stored like a multi-link: the database foreign key is stored at one association end. The Designer marks the end that stores the key with a small (e) for embedded.

Place (e) on one end of the association only.

Technically, a key can be stored at both ends, but doing so creates two stored representations of the same relationship. They can become inconsistent. Use one embedded end so that there is one foreign key and one authoritative stored value.

For example, consider an optional association between ConsolidationRequest and Winner:

• A ConsolidationRequest has zero or one Winner.
• A Winner belongs to zero or one ConsolidationRequest.
• If the WinnerID key is embedded on ConsolidationRequest, each request row stores its selected winner.

The foreign key alone verifies that a non-null WinnerID refers to a valid winner. It does not inherently ensure that the same winner is used by only one request. That second rule requires uniqueness on the foreign-key column.

Why an optional one-to-one link needs a filtered index

A one-to-one association must prevent this situation:

ConsolidationRequest	WinnerID
Request A	42
Request B	42

Both requests refer to WinnerID 42, so the association is no longer one-to-one.

A normal unique constraint on WinnerID is not appropriate when the association is optional (for example, 0..1 at the embedded end). Multiple requests may legitimately have no winner yet, and therefore have a null WinnerID. On databases where a normal unique constraint permits only one null, it would incorrectly reject valid unassigned requests.

A filtered unique index applies uniqueness only when the foreign key has a value. In SQL Server, the required pattern is:

CREATE UNIQUE NONCLUSTERED INDEX IX_test
ON ConsolidationRequest (WinnerID)
WHERE WinnerID IS NOT NULL;

With this index:

• WinnerID = 42 can occur in only one row.
• Multiple rows with WinnerID = NULL are allowed.
• The database enforces the intended optional one-to-one relationship even if data is written outside the model logic.

Database support in MDriven

MDriven database generation uses filtered indexes for this purpose where the database configuration says they are supported. The database-configuration flag is SupportsFilteredIndex.

Database configuration behavior described for this feature	SupportsFilteredIndex
Microsoft SQL Server	true
SQL Server Compact (SQLCe)	Not enabled
MySQL	Not enabled

Do not enable this behavior for a database engine unless that engine supports a unique index with a non-null filter. Database engines differ in their support for filtered or partial indexes.

Design checklist

1. In MDriven Designer, create the association with the intended single-link multiplicities.
2. Verify that only one association end has the (e) marker.
3. Decide whether the embedded foreign key may be null. An optional association requires multiple null foreign-key values to remain valid.
4. For an optional one-to-one link on a database that supports it, use the filtered-unique-index behavior so that non-null foreign-key values are unique.
5. Test both cases in the generated or existing database: create several objects with no linked object, then attempt to assign the same linked object to two owners. The first case must succeed; the second must fail.

Keep database integrity and model rules distinct

Association cardinalities are implicit model constraints. You can add further business rules as class constraints, but an application-level rule does not replace the database uniqueness requirement when the database must protect the relationship from duplicate foreign-key values. For guidance on defining and presenting model rules, see Training:Constraints and Documentation:ViewModel validations.

Use OCL for declarative expressions that inspect model state. Use constraints and guards for business-rule validation; use the filtered unique index for this stored one-to-one key invariant.

See also

• Documentation:Learn OCL
• Training:Constraints
• Documentation:GuardConstraints
• Documentation:Derived settable associations
• Documentation:Missing Set button in Autoform single link