Enforce Unique Values for external data type column in sharepoint

Question

Enforce Unique Values for external data type column - I know that it is not possible out of the box. What are the soultions for validating external data type columns for duplicates? workflows? others?

Answer 1

Well, external lists can't have workflows or event receivers where you could validate data, so doing this in SharePoint would actually be very complicated. My opinion is that you should validate your data before importing it into SP. If your datasource is a DB then add a constraint, if it's a web service then the external system should enforce uniqueness, if it's a custom external content type you can enforce it through code.

Answer 2

Though it doesn't seem to mention it in the MS Documentation you can Enforce uniqueness onto an 'External Data' type column using PowerShell. I've just tried the example below and it works on an SP2013 Farm.

https://msdn.microsoft.com/en-us/library/office/ee536168%28v=office.14%29.aspx?f=255&MSPPError=-2147217396

Example from Office DEV Center

SPSite site = new SPSite("http://localhost");
SPWeb web = site.OpenWeb();

SPList custList = web.Lists["Customers"];
SPField custPhone = custList.Fields["Phone Number"];

custPhone.Indexed = true;
custPhone.EnforceUniqueValues = true;

/// You must call the Update() method 
/// when you change the EnforceUniqueValues property
custPhone.Update();