You can resolve a SQL Server Cannot insert duplicate key row error caused by replacing a single link by clearing and saving the existing link before you assign its replacement.

When this error occurs

A single link is a relationship where an object can have at most one object at that end of the relationship. SQL Server enforces that constraint with a unique key.

This error can occur when you replace an existing single link directly with a new link. The database can attempt to insert the new relationship before the old relationship has been removed, which violates the unique-key constraint.

For example, assume an object has a single link to one related object:

• The object is currently linked to A.
• You change the link directly from A to B.
• SQL Server reports Cannot insert duplicate key row because the existing link has not yet been persisted as removed when the new link is inserted.

This problem mainly occurs with SQL Server.

Resolve the error

Replace the link in two saved operations rather than one.

1. Set the existing single link to null. This removes the current relationship.
2. Save the change so that the removal is persisted to the database.
3. Assign the single link to the new related object.
4. Save again.

Using the example above, do not change the link from A directly to B. First remove the link to A and save. Then create the link to B and save.

Check for existing duplicate data

If the error continues, the database may already contain duplicate values for a single-link column. Use the consistency checks in Documentation:Validate data in the database to find the affected table and rows.

In MDrivenServer:

1. Log in to MDrivenServer.
2. Go to Settings -> Admin.
3. Run the available database validation check for the application database.
4. Review the reported SQL statement for Single link dupes and run that statement in your SQL Server query interface to identify the offending rows.

A report can, for example, group a link column and show values that occur more than once. Each reported value represents a relationship that does not meet the single-link constraint.

Correct invalid data carefully

Do not delete rows directly in SQL Server unless you have accounted for every reference to those rows. Direct deletion can leave references and aggregate objects in an inconsistent state.

When a duplicate single link is identified, the recommended approach is:

1. Set the link attribute that causes the duplicate to null to remove the duplicate relationship.
2. Use the OCL debugger to find the now-disconnected object.
3. Delete the object through MDriven so that related aggregate objects, links, and link objects are handled correctly.

After the correction, run Documentation:Validate data in the database again to confirm that the duplicate single-link report is clean.

See also

• Documentation:Validate data in the database
• Documentation:Custom OR Mapping
• HowTos:Troubleshoot Turnkey Deployment