​Column 'X' is Constrained to be Unique

Error Example:

An error occurred while executing connection '<connectionName>': The row with key <keyValue> could not be added to the table <tableName> due to the following error: 'Column '<columnName>' is constrained to be unique. Value '<value>' is already present.'.

Note this may be an older version/variant of this error:

Error reading from entity '<entityName>': Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints. The column '<columnName>' in row '<rowId>' is indicating the following error: Column '<columnN​ame>' is constrained to be unique.  Value '<value>' is already present. ​

Cause:

This error is generally caused when the application is attempting to insert a value into a column that has been marked as unique, as a primary key, or an identity (depending on the source) that already has that specific value (or multiple null values).

There are also some specific special cases that can lead to this error as well:

• If the sync is for folders and files, there are particular reasons this error appears due to the FilePath being ​the primary key. See Failed to Enable Constraints - 'FilePath' is Constrained to be Unique​.
• If the defined Primary Key column is also set as the column performing a join in the select statement query, this error can occur.
• If using a Dynamic Column as the Primary Key. You cannot use these special columns for this.
• If using an updatable view instead of a table for the select statement, if a column is marked as unique in the original table, the view will inherit this and must follow the same uniqueness criteria (as it has to write the value back to the unique column in the table).
• If the primary key is set to a Date/Time-type column, it can lead to this as the timestamps may not be unique.

Resolution/Workaround:

The General Resolution below covers the common cause for this error. Below are additional resolutions for the specific special cases mentioned in the Cause section above.

General Resolution

In most cases, the error itself lists the problematic column and value, you can use this information to go to the data source and fix the issue so that the values are now unique for that column.

This may also be a symptom of other issues, like a poor choice in a primary key, especially with combo ones; truncation of values; or improperly set constraints. If there are no clear duplicate values, check the trace log to see if the values are getting changed in the sync process and check the combination keys are actually unique and that the columns that should be unique are set properly.

'FilePath' is Constrained to be Unique

If the column in the error is 'FilePath' and you are syncing to SharePoint, see this specific article about that issue: Failed to Enable Constraints - 'FilePath' is Constrained to be Unique

Primary Key Column is also Query Join Column

You cannot do this - you need to use either another primary key or a different value to join tables in the query.

Using Dynamic Column as Primary Key

You cannot do this as this column type is variable and may not be unique over time - you must select another non-Dynamic Column as the primary key.

Using Updatable View for Query

The view will inherit any constraints from the original table(s), so you still must follow those when writing values via the view back to the tables in SQL. You may have to change your query if you expected the column to be non-unique when used in the view.

Using Date/Time-Type Fields as Primary Key

Timestamps are not always unique so you may get this if you are using date or date/time columns as the key or as part of a combination key. There are also some additional errors that can occur from this, so it is not supported to use Date/Time fields as the primary key. You need to change the defined primary key to not use Date/Time columns.

Additional Error Text Variations

For a variation on this error specifically addressing custom primary key constraint errors, see Error Reading Back Keys Because Table 'X' No Longer Has a Primary Key Defined​.

There is also this Key-specific variation of the error, The Primary Key 'X' is Not Unique.