Unique Column Constraints in SharePoint 2010

In SharePoint 2007 you couldn't enforce uniqueness on a column in a list or document library to ensure that the column would never have duplicate values, i.e., to ensure that no two list items in the list would have the same values for the column. In SharePoint 2007 the only column that supports uniqueness is the ID column. In other words, no two list items in the same list can have the same values for the ID column.

Things have changed in SharePoint 2010. You can now enforce uniqueness on the columns of the following types: Single line of text, Choice field (but not multi-choice), Number, Currency, Date/ Time, Lookup (but not multi-value), Person or Group (but not multi-value), and Title (but not in a document library)

You can enforce uniqueness on columns of the above types provided that you have enabled indexing on the column. In other words, only indexed columns support uniqueness. You cannot turn off indexing on a column as long as the uniqueness is turned on on the column. You have to turn off uniqueness before you could turn off indexing.

Also note that uniqueness is case-insensitive. For instance, let's say you have a column named Product Name in a Products list. If you've turned on uniqueness on this column, you cannot add two items with the following two values for this column: Product1 and product1. This is because these two values are considered equivalent.

Note that lookup columns also support uniqueness, but what does uniqueness means in the case of a lookup column? A uniqueness in this case means that more than one list item in the child list cannot look up to the same list item in the parent list.

Next, we'll see how we can create a unique column through SharePoint user interface and through object model.

Creating a unique column through SharePoint user interface

Create a custom list named Products and click the Create Column button in the ribben to launch the Create Column dialog:

Name the new column Product Name and select the Single line of text option as its data type. Scroll down to see the Additional Column Settings section of the dialog:

Note that this section contains a new option titled Enforce unique values, which is off by default. In other words, by default all columns (off course except for the ID column) accept duplicate values. Select Yes to enforce uniqueness on the Product Name column. Scroll down to see the Column validation section:

This section allows you to add validation formula to this column. When a user is entering a new value for this column or updating its value, this formula will automatically execute to validate the user input. Note that this validation is performed at the data store level not the user interface layer. This means that if you write custom code to enter a value for this column or change its current value, this formula will be automatically executed to validate your value.

As you can see, the following three features on a column bring SharePoint columns very close their database counterparts in terms of integrity constraints:

• Require that this column contains information
• Enforce unique values
• Column validation

If you click the OK button in the previous screen, you'll get the following message:

The message says that the Product Name must be indexed to enforce unique values. As discussed earlier, only indexed columns support the unique column constraints feature. Click OK.

Create a unique column through code

First go ahead and delete the Product Name column through SharePoint user interface because we want to add the same field through code. Following the steps discussed in one of my previous post to create an empty SharePoint project with an application page. Then use the following as the code behind for this application page:

using System;
using Microsoft.SharePoint;
using Microsoft.SharePoint.WebControls;

namespace DemoSharePointProject.Layouts.DemoSharePointProject
{
public partial class ApplicationPage1 : LayoutsPageBase
{
protected void Page_Load(object sender, EventArgs e)
{
using (SPSite site = new SPSite("http://win-wzqi6cnoktp:45610/"))
{
using (SPWeb web = site.OpenWeb())
{
web.AllowUnsafeUpdates = true;
SPList products = web.Lists["Products"];
SPFieldCollection fields = products.Fields;
fields.Add("Product Name", SPFieldType.Text, true);
SPField productName = fields["Product Name"];
productName.Indexed = true;
productName.EnforceUniqueValues = true;
productName.Update();
}
}
}
}
}

Now if you access the application page, it should add the Product Name as an indexed unique column to the Products list.

As you can see from the above code, the SPField base class supports two new boolean properties in SharePoint 2010 named Indexed and EnforceUniqueValues. As discussed earlier you have to set the Indexed property to true before you can set the EnforceUniqueValues properties to true.