Failed to Enable Constraints When Using SQL Connector

Issue

At runtime, the SQLConnector throws an exception with a message: “Failed to enable constraints.  One or more rows contain values violating non-null, unique of foreign-key constraints.”

The error message that will be displayed is like the image below:

This is a very generic error message that is raised by the .NET framework when it executes the query and populates the underlying dataset that is created by the SQLConnector.

Resolution

This problem is usually caused by one of the following:

• Null values returned for columns not set to AllowDBNull.
• Duplicate rows being returned with the same primary key.
• A mismatch in column definition (e.g. size of character field) between the database and the dataset.

To resolve this issue, try the following:

• If the query was changed or the underlying tables/views used have been changed then remap the query for the SQLConnector using the Integration Wizard. This is especially true if a new field was added to the query.
• Run the query natively and look at the results, if the resultset is not too large. If you’ve eliminated null values, then the problem usually is that the primary key column is being duplicated.
• Include all the key columns and the columns defined as “not null” into your queries.

If none of the above solves the problem look at the results returned here.