Violation of PRIMARY KEY Constraint 'CNS_NAME'. Cannot Insert Duplicate Key in Object 'TABLE_NAME'.

Symptoms

During import to SQL Server the message with the following content may occur: “Error = [Microsoft][SQL Native Client][SQL Server]Violation of PRIMARY KEY constraint 'CNS_NAME'. Cannot insert duplicate key in object 'TABLE_NAME'”.

Cause

As the default SQL Server database has case-insensitive collation and the data is compared in case-insensitive manner. So, if the source database had case-sensitive data comparison you may receive this kind of message for any constraint that presupposes the uniqueness of data in the column, e.g. Primary Key and Unique Index.

Solution

To handle this problem it is recommended to create SQL Server database using binary or case-sensitive collation.

To do this during the target database creation please choose the appropriate collation from the list in the “Collation:” option on the “Options” page of the “New Database” creation dialog page, that would contain 'BIN' or 'CS' letters in it's name. Please refer to the screen-shot below for the reference:

NOTICE: Please note that case-sensitive collation also influences the case-sensitivity of the object names in the database - they also become case-sensitive.

If you have any other questions, please contact us: support@ispirer.com