Documentation:Duplicate IDs in the database

Duplicate IDs in the database

This page was created by Lars.olofsson on 2023-02-20. Last edited by Wikiadmin on 2026-07-29.

You can diagnose and repair ambiguous single-link data reported by MDrivenServer when more than one database row points to the same target through an association that permits only one link.

Recognize the error

MDrivenServer can write a message similar to the following to its log:

Ambiguous values for <class>.<association> for id <xxxxx>. The following objects (ids) all refer to the same target: yyyyy, zzzzz

This message means that MDrivenServer found multiple rows that use the same link ID for a single link. A single link is an association end that allows one object at that end, not a collection of objects.

For example, if each AgreementImport row has one PublisherAgreementPeriod link, two or more AgreementImport rows must not hold the same PublisherAgreementPeriodID when that association is modeled as a single link. The values yyyyy and zzzzz in the message identify the rows that conflict; both point to the target identified by xxxxx.

Why this happens

In the SQL database, persistent classes are stored as tables and objects are stored as rows. Associations are represented with ID columns that connect those rows. See Documentation:SQL Database for the storage model.

The database now contains links that do not match the multiplicity in your model. This can occur after direct database changes, imports, or a failed or incorrect update of association data. Do not treat the message as a normal warning: MDrivenServer cannot unambiguously materialize the modeled single link from the conflicting rows.

This error concerns duplicate values in a link column. It does not necessarily mean that two rows have the same primary-key ID.

Find the conflicting rows

Use MDrivenServer's database validation report to obtain the query for the exact table and link column.

Log in to MDrivenServer.
Select Settings and then Admin.
Run Validate A0DB Data for the application-data database. If the issue concerns MDrivenServer's internal model database, run Validate Admin DB Data instead.
Review the report for an entry beginning with Single link dupes.
Copy the SQL select statement supplied in that report into your SQL Server query interface and run it.
Inspect the returned rows. Identify which row should retain the link and which row or rows have an incorrect link value.

A validation report can contain output like this:

Single link dupes(38146711), execute
select PublisherAgreementPeriodID,Count(PublisherAgreementPeriodID)
from AgreementImport
where not PublisherAgreementPeriodID is null
group by PublisherAgreementPeriodID
having Count(PublisherAgreementPeriodID)>1

The query groups non-null link values and returns only values used more than once. It identifies the duplicate link value; use further inspection of the affected table and the application data to decide which relationship is valid.

Repair the data safely

Correct the incorrect link value first. The recommended approach is to set the link attribute that creates the duplicate to null, disconnecting the incorrect relationship.

Determine the row whose link is incorrect from the validation query and your business data.
Set that row's affected link attribute to null. Do not remove a row until you have considered all of its references and aggregate objects.
Use the OCL debugger to locate the now-disconnected object with OCL or OclPS.
Delete the disconnected object through the debugger when it is the object that should be removed. This allows MDriven to correctly delete linked aggregate objects, links from other objects, and link objects.
Run Validate A0DB Data again and confirm that the Single link dupes report is clean.

Do not start by deleting the offending database row with SQL. A direct delete can leave other rows pointing to an object that no longer exists and create additional consistency problems. For related checks and the SQL patterns used to find broken links, see Documentation:Database corruption.

Related SQL Server duplicate-key error

A SQL Server message stating that it cannot insert a duplicate key row is a related but different failure. It can occur when you assign a new value to a single link. The documented workaround is to save the link after assigning it null, then assign and save the new link. Follow HowTos:Resolve 'Cannot insert duplicate key row' SQL Error for that procedure.

Prevent recurrence

Keep association changes in MDriven's modeled data flow rather than editing persistent link-ID columns directly in the database. After imports or database maintenance, run database validation before users encounter ambiguous data.

Also verify the database isolation settings when investigating wider integrity issues. An update isolation level that is too low can cause new IDs and timestamps not to be unique. See Documentation:IsolationLevel. For integration-facing identity, use a GUID rather than a database row's integer ID; see Documentation:Object Identity and Documentation:Guid.

Contents