How to prevent duplicate entries in SharePoint lists and libraries with “enforce unique values” feature

There are situations when you have a list or library and need to assure it only contains unique metadata values. With this post, I will explain a little-known functionality called “enforce unique values” and how you can use it to prevent duplicate entries in SharePoint lists and libraries.

What is “enforce unique values” feature?

Enforce unique values is a setting on a SharePoint metadata column. When you create a new metadata column, you can check off the appropriate radio button to enable it (it is disabled by default).

How does it Work?

Once enabled, it assures that the value chosen for a specific field in the whole SharePoint list or a Document library can only be chosen once. For example, if you set enforce unique number on a drop down column with department names, and say tag one entry against HR (Human Resources), no one (including you, my friend) will be able to tag other rows with that same value.

What is the end user experience like?

Honey, what’s up with all these questions? Just kidding :-)  When the users try to tag an item with the value that’s has been already chosen previously, they get the following error message: “This value already exists in the list.”

Example of the error message on the Modern SharePoint list

Example of the error message on the Classic SharePoint list

Example of the error message if you use Quick Edit function to tag items or documents

How to prevent duplicate entries in SharePoint lists and libraries

Back to the original topic, how do we prevent duplicate entries in SharePoint lists and libraries? The trick is to enable the Enforce unique values setting on a metadata column.

Whether you work with a SharePoint list or a SharePoint library, when creating new columns, just scroll down at the column creation screen and enable the setting to Enforce unique values.

You can also enable the setting for an existing Title Column in a SharePoint List. Just edit the column and enable the setting to Enforce unique values.

NOTE:

You cannot enforce unique values on a Title field in a SharePoint document library. I think you will agree that it does not make sense.

Use Cases

So what are the use cases for unique metadata values in SharePoint lists and libraries? Here are a few I can think of:

• You maintain a list of projects or clients in a SharePoint list and want to make sure you do not have a duplicate entry (unique value on the Title or custom text column)
• You maintain a library of company policies and want to make sure Renewal date does not fall on the same date for more than one policy document (unique value for a Date field)
• You have an Issues/Action Log and want to make sure your team does not log the same issue twice (unique value on the Title or custom text column)
• You have a Task List and want to make sure each user has a maximum of 1 task assigned from the list (unique value on the People column)
• You use a custom list as a checklist and want to make sure all entries are unique (unique value on the Title or custom text column)
• You use a document library to organize various policies (files) that are part of your company handbook and want to make sure your chapter and section numbers are unique and cannot be assigned to more than 1 document (unique value on the drop-down or custom text column)
• You created a custom column with a sequence of numbers for list ordering and want to make sure each entry has a unique entry order assigned (unique value on a number)

Additional reading

This post provides a good reference on the feature and especially its limitations (which columns are supported and which ones are not).