Unique column constraint

In SharePoint 2007, we couldn't set unique enforcements to each column or fields to avoid duplicate values in a list or library. "ID" is the only one having unique constraint in SharePoint 2007.

Before to know about enforce uniqueness feature better to get an idea on how can we handle the duplications on list items in SharePoint 2007.

 How can we set the enforce uniqueness to the columns in SharePoint 2007:

Best way to achieve enforce uniqueness to the columns in SharePoint 2007 is event handler. We can handle it by comparing the column values in ItemAdding event.

SharePoint came up with the enforce uniqueness feature. We can handle the enforce uniqueness easily through UI only for the newly created columns.

Enforce uniqueness in SharePoint 2010:

1. SharePoint 2010 came up with that fantastic new feature called as “Unique column constraint“to avoid duplications. The unique fields are case insensitive.
2. SPField has a new property called “EnforceUniqueValues”. It is of type Boolean and set asfalse [means allow duplicates] by default.
3. We can set the enforce uniqueness for the indexed columns only.
4. List is indexed automatically when SPColumn is defined as Unique Column Constraint.
5. We can’t set uniqueness for all the columns. Please refer to know which all those are.
• http://msdn.microsoft.com/en-us/library/ee536168.aspx

Limitations:

• Unique Column constraint does not work on SPFolder level.
• Possibility level to set the Uniqueness of Column is at SPList only.
• We can’t set the enforce uniqueness for the already created columns if that columns has duplicate data.

Exceptions:

“The field must first be indexed to enforce unique values." This is will come if you try to set the “EnforceUniqueValues = true” to the non-indexed fields.

“This field contains duplicate values. Remove all duplicate values and try the operations again." This is will come if you try to set on a list that has existing duplicate values.