This column cannot enforce unique values because this list or document library may contain items that are not viewable by all users.

Question

_{Sunday, September 23, 2012 4:36 AM}

The above error occurs when I place unique constraint on a column in a custom list. This error only occurs because the custom list has been configured to only allow creaters to view and edit their own list items.

I just can't see why that should prevent SP from placing a unique value constraint though.

One easy workaround would be to place the unique constraint on the SQL Server table that represents this list. However, in a hosted environment, it is not always possible to gain access to the backend server. What is more, I am uncomfortable with this approach as it may break other parts of SP 2010.

Is there any other approach?

All replies (4)

_{Monday, September 24, 2012 3:22 AM ✅Answered | 1 vote}

Hi,

If SharePoint were to enforce uniqueness on a column in a list where users are only allowed to see and edit list items that have created, then you could have a scenario where -

a. user1 tries to add a new item to the list which violates the uniqueness constraint

b. SharePoint reports an error

c. user1 gets to know that there is another item containing the same value in this list

d. this violates the security settings that you configured (each user should be allowed to only see the items that they have created). Now if you change the permissions settings to allow users to SEE all items, but only edit items that they have created, this error disappears (obviously).

Now getting to the workaround you mentioned (creating a unique constraint on the SQL Server table). This is not going to be possible for numerous reasons -

a. Modifying (or for that matter, even performing SELECT operations on) the SQL Server tables is an unsupported operation and it will definetely break other things in SharePoint, not to mention that you will lose all support options from Microsoft. However if you don't care about this, then it will still not be technically possible because...

1. SharePoint does not create a new SQL Server table for every list/library. 

2. If you did get around to enforcing a unique constraint in the one single table that SharePoint uses for all lists, then you will be enforcing this constraint on each and every list in all the sites and in all the site collections that are assigned to that content database.

Please "Mark as Answer" if a post has answered your question or "Vote as Helpful" if it was helpful in some way. Here's why

_{Monday, September 24, 2012 2:44 PM}

Thanks for the response. While I can understand the rationale of disabling unique constraint in SP due to security restrictions, I don't believe it really meets common business requirement.

It's very frequent that an organisation places unique constraints on values such as case number, invoice number, customer number etc - which by itself would not reveal additional information without having access to the other fields within the list. Given how common this business scenario is, I would have thought MS Sharepoint ought to have handled it as OOTBF.

To clarify on the solution I used, I oversimplied when I said I placed a unique constraint on the SQL Server sharepoint table. What I did is to write a CLR trigger on the ALLLists table. The CLR trigger decompresses the tp_Fields column for the specific list I am interested (stripping the first 14 bytes and then call the IO.Decompress method, and then convert to XML). From there, I am able to examine the data and determine if the unique constraint has been broken on the specific list I am interested and roll back if necessary. I was very loathe to use this method for reason apart from those I mentioned in my original post that it requires writing code in VS - which is something I am trying to avoid. While developing CLR is still much simpler than developing custom Sharepoint functionality in VS (at least for me), my original goal has been to stick with configurations using either the SP web interface or SPD.

_{Monday, September 24, 2012 2:58 PM}

I agree with you that most common business requirements would need the unique constraints to work in the way you describe. However i guess the SharePoint team took a design decision to ensure that security constraints are respected first over and above other constraints. So that's the way it is in SharePoint OOTB.

Please "Mark as Answer" if a post has answered your question or "Vote as Helpful" if it was helpful in some way. Here's why

_{Wednesday, April 29, 2015 9:29 AM | 1 vote}

I too faced same issue, I enforced unique constraints to column by editing column using List settings. Before that I allowed read all items from Advanced settings menu. My requirement was to restrict users only to see entries which they have added, i achieved this using filter condition in view(Created By=[Me].