How to add unique key constraint on CRM entity column RRS feed

  • Question

  • Hello,

    I am trying to create an unique key constraint on the CRM entity via database.

    When I execute the alter script it throws an error as,

    Msg 1505, Level 16, State 1, Line 1

    The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.LeadBase' and the index name 'uc_lead'. The duplicate key value is (<NULL>, <NULL>).

    Msg 1750, Level 16, State 0, Line 1
    Could not create constraint. See previous errors.
    The statement has been terminated.

    There are some records in the database which have null values.

    How can we add the constraint from database?

    • Edited by Vijoy2015 Friday, February 19, 2016 5:37 AM more information added
    Friday, February 19, 2016 5:19 AM

All replies

  • Hello Vijoy,

    Why don't you use "Duplicate Detection Rules" from inside the Dynamics CRM settings area, to achieve the same? This way you must be able to enforce the rule with existing null values also.

    blog: http://technologynotesforyou.wordpress.com | skype: ali.net.pk

    • Edited by Khadim Ali Friday, February 19, 2016 7:55 AM
    Friday, February 19, 2016 7:54 AM
  • You can have the index ignore null values using the following syntax when creating the index:

     ON LeadBase([MyCol])  WHERE ([MyCol] IS NOT NULL) 

    However, I would advise against creating unique indexes in the database. Although it is supported to create indexes on the CRM tables, a unique index forms a type of constraint, and the CRM platform will not know how to handle this. If a user does try to save a record with a duplicate value, at best the message they get back will be 'A SQL Error has occured'.

    Instead, I suggest you use duplicate detection rules (as per the previous post), or maybe write a plugin to check for duplicates, which would allow you to control the error given to the user

    Microsoft CRM MVP - http://mscrmuk.blogspot.com/ http://www.excitation.co.uk

    Friday, February 19, 2016 10:46 AM