Enforcing Uniqueness in Column Values

Article
10/20/2016

Applies to: SharePoint Foundation 2010

You can enforce uniqueness on values in a list or library column, effectively creating a primary key. While previously the identity (ID) column was the only means to provide uniqueness on a list or library, Microsoft SharePoint Foundation 2010 introduces a new feature called unique column constraints, which allows you to enforce uniqueness.

Defining "Unique"

Clearly, declaring uniqueness is not a trivial issue, so it is important to define precisely what is meant by "unique." SharePoint Foundation evaluates column values and determines uniqueness based on its evaluation of the values present in the column. For the evaluation, SharePoint Foundation uses the sort order of the site (the SPWeb) to do uniqueness comparisons. Note that the comparison is case-insensitive, so the values "hello world" and "Hello World" are evaluated as equivalent.

Supporting Unique Column Constraints

The key factor in determining whether a column supports uniqueness constraints is whether that column can be indexed. Furthermore, when uniqueness is enforced on a Lookup column, the list item in the target list can have only one list item looking up to it from the child list (the list where the Lookup column lives). In other words, uniqueness is not enforced on the projected column of the target list but rather on the ID column. Column types that support the ability to be indexed, and those that are not, are listed below.

Supported Column Types

Following is a list of column types that can be indexed and for which unique column constraints are supported:

Single line of text
Choice field (but not multichoice)
Number
Currency
Date/ Time
Lookup (but not multivalue)
Person or Group (but not multivalue)
Title (but not in a document library)

Unsupported Column Types

Following is a list of column types that cannot be indexed and for which unique column constraints are not supported:

Multiple lines of text
Hyperlink/Picture
Custom Field Types
Calculated Field
Boolean (yes/no)
Modified by
Modified time
UI version
Created time
Checked out to
Content type ID

Indexing a Column with Unique Constraints

As stated, a column that enforces a uniqueness constraint must be indexed. When the user selects Enforce unique values and clicks OK, a warning dialog is displayed if the column is not already indexed; the user is then given the option to automatically index the column. After a column has been set to enforce uniqueness, indexing cannot be turned off for that column. However, you can turn off indexing for that column if you first disable enforcement of uniqueness constraints.

Setting Unique Constraints Programmatically

Use the EnforceUniqueValues property on the SPField object to set a column to require unique values. The property gets and sets a Boolean value to specify whether duplicate values are allowed; the default is to allow duplicate values, so you must explicitly set a column field property to true.

The following code example illustrates changing a field to require unique values. Notice that the field must be explicitly updated after changing the property value.

SPSite site = new SPSite("https://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();

Error Cases - Exceptions

There are two error cases associated with using the EnforceUniqueValues property to enforce uniqueness:

EnforceUniqueValues = true is set on a non-indexed field.
Throws an exception object (SPException) with a message reading "The field must first be indexed to enforce unique values."
EnforceUniqueValues = true is set on a list that has existing duplicate values.
Throws an exception object (SPException) with a message reading "This field contains duplicate values. Remove all duplicate values and try the operations again."

Content Migration and Unique Column Constraints

Content migration by using APIs in the Microsoft.SharePoint.Deployment namespace supports both partial (selective) and full site collection migrations by using export/import functions. When using the deployment APIs in full migration scenarios, you must ensure that if the uniqueness property is set on the list field on the export source (that is, if the EnforceUniqueValues property is set to true), then the setting must be persisted on the import target.

However, when you are doing a partial (or "selective") migration in which you merge files from the source site collection with files on the target, the operation will automatically account for uniqueness states, as outlined here.

Unique Columns on Source, Nonunique on Target

Items are imported to the destination, or target.
Then, the EnforceUniqueValues property is set to true on the target.

Note that this operation could fail if duplicate items exist on the list on the target in violation of the uniqueness constraint. If this occurs, the uniqueness property value will not be set and the attempt returns a nonfatal error.

Unique Columns on Source, Unique on Target

Perform the export/import on the list items normally.

This operation will fail if an item in the import package violates the uniqueness constraint. If this occurs, the item will not be imported and the attempt returns a nonfatal error.

Nonunique Columns on Source, Unique on Target

First, disable uniqueness on the field on the target list by setting the EnforceUniqueValues property to false on the target.
Import list items to the target normally.

Note

The three scenarios listed above are also valid in cases where the RetainObjectIdentity property is used on import. In this case, rather than importing a duplicate of the item on the target, items that already exist on the target are instead updated to match the item that is exported from the source.

Copy-To and Move-To Operations with Column Constraints

Special considerations come into play whenever you copy or move items to a document library that is enforcing unique column constraints and you use the CopyTo(String) and MoveTo(String) methods on the SPFile class to perform these operations. You must be aware of this in scenarios where you move or copy a file into such a library.

Because there is potential for data loss in MoveTo() scenarios, and because this potential is much diminished in CopyTo() scenarios (because the file at the source is not removed), the general rule to follow is to block unique column constraints in MoveTo() operations but allow them in CopyTo() operations. The following matrix details the specifics:

	Copy To operation	Move To operation
Within Same Doc Library	Checks for uniqueness violations at the document level. Does not check across library folders.	Moves without checking.
Across Doc Libraries	Converts values to null.	Blocks the move if the destination list has enforced unique values.
Out of Doc Library	No effect.	No effect.
Into Doc Library	Convert values to null.	Convert values to null.