duplicate key value is NULL

Article
09/21/2017

Question

_{Thursday, September 21, 2017 7:19 PM}

During data migration of a table I get the following error:

Violation of UNIQUE KEY constraint .... Cannot insert duplicate key in object .... The duplicate key value is (<NULL>).

Analysis report did not show any error for this table, but data migration then shows above error.

Help is appreciated.

Andreas

All replies (4)

_{Friday, September 22, 2017 6:38 AM ✅Answered}

Hi Andreas,

>> Violation of UNIQUE KEY constraint .... Cannot insert duplicate key in object .... The duplicate key value is (<NULL>).

What is your DDL statements on the destination table? Does your source column contain multiple null value? If you have a unique constraint on the column, consider dropping it and adding unique nonclustered index instead. See code example below:

USE [databaseName]

CREATE UNIQUE NONCLUSTERED INDEX [NonClusteredIndexName] ON [tableName]

(

[columnName] ASC

)

WHERE [columnName] IS NOT NULL

>> Analysis report did not show any error for this table, but data migration then shows above error.

What tool are you using to make the analysis report?

If you have any other questions, please let me know.

Regards,

Hannah

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

_{Friday, September 22, 2017 6:02 AM}

Hello Andreas,

Your post is not clear for me; who does the Migration Looks like in detail? Against this the error message itself is quite clear.

Olaf Helper

[ Blog] [ Xing] [ MVP]

_{Friday, September 22, 2017 6:43 AM}

Hi Hannah

Indeed my source table contains multiple NULL values. I will fix this.

I am using SSMA for Oracle 7.5 and run Report function very first to analyze my source database.

Andreas

_{Friday, September 22, 2017 9:16 AM}