Unique constraint allow null sql server

But to achieve this, we need to sacrifice the space utilised by the additional column i. Please see the below sample script which works as second solution. The above two solutions would work on any version of SQL, when it comes to SQL Server we have another solution by using filtered index which can replace the second solution above, by simply creating the below UNIQUE filtered index. Like us on FaceBook Follow us on Twitter. May 4, May 4, April 27, April 27, April 22, April 22, Your email address will not be published.

Save my name, email, and website in this browser for the next time I comment. Skip to content A unique index guarantees that the index key contains no duplicate values and therefore every row in the table is in some way unique. If the data is unique and you want uniqueness enforced, creating a unique index of a non-unique index on the same consideration of columns provides additonal information for the query optimizer that can produce more efficient plans. A unique nonclustered index can contain included non-key columns.

You can try your own method—be sure you understand the domain of your data so that there is no possibility of intersection with real data.

That could be as simple as prepending a differentiator character like this:. Note that the presence of an index including the computed column implicitly causes each expression result to be saved to disk with the other data in the table, which DOES take additional disk space.

Please note that some database professionals will see this as a case of "surrogate NULLs", which definitely have problems mostly due to issues around trying to determine when something is a real value or a surrogate value for missing data ; there can also be issues with the number of non-NULL surrogate values multiplying like crazy.

However, I believe this case is different. The computed column I'm adding will never be used to determine anything. It has no meaning of itself, and encodes no information that isn't already found separately in other, properly defined columns.

It should never be selected or used. Have fun adding a new column to your base table you'll at minimum have to drop the index, and then drop the view or alter the view to not be schema bound. See the full long list of requirements for creating an indexed view in SQL Server also later versions , If your column is numeric, there may be the challenge of ensuring that the unique constraint using Coalesce does not result in collisions.

In that case, there are some options. One might be to use a negative number, to put the "surrogate NULLs" only in the negative range, and the "real values" only in the positive range. Alternately, the following pattern could be used. Some thought will show that this constraint cannot be duplicated for any row in the table, and still allows multiple NULLs. For people who are using Microsoft SQL Server Manager and want to create a Unique but Nullable index you can create your unique index as you normally would then in your Index Properties for your new index, select "Filter" from the left hand panel, then enter your filter which is your where clause.

It should read something like this:. I found this on MSDN. With a non-clustered non-unique index on the column to enable the lookup. This often suggests you're creating a separate sub-entity within the same table as a different entity. It probably makes more sense to have this entity in a second table. In the provided example, I would put LibraryCardId in a separate LibraryCards table with a unique not-null foreign key to the People table:.

This way you don't need to bother with a column being both unique and nullable. If a person doesn't have a library card, they just won't have a record in the library cards table. Also, if there are additional attributes about the library card perhaps Expiration Date or something , you now have a logical place to put those fields. There is a ticket on Microsoft Connect for this since As suggested there and here the best options as of today are to use a filtered index as stated in another answer or a computed column, e.

Creating an index can be costly on larger tables. Stack Overflow for Teams — Collaborate and share knowledge with a private group. Create a free Team What is Teams? Collectives on Stack Overflow. Learn more. How do I create a unique constraint that also allows nulls? Ask Question. Asked 12 years, 9 months ago. Active 9 months ago. Viewed k times.

Improve this question. Jeroen Stuart Stuart Connect issue for standard compatibility to vote for: connect. It is common sense. It is not possible — flik. That is no valid argument. Especially when considering that null is not a value but the absence of value. Per the SQL standard, null is not considered equal to null.

So why multiple null should be an uniqueness violation? Add a comment. Active Oldest Votes. Improve this answer. Vincent Buck Vincent Buck Any ideas? It would be nice to be able to have a nullable column that is always unique when not null be the source of a Foreign Key relationship. Truly a great answer. Too bad it was hidden by the one accepted as answer.

This solution almost didn't get to my attention, but it works like wonders in my implementation now. Show 11 more comments. Community Bot 1 1 1 silver badge. Jose Basilio Jose Basilio If you are using SQL Server or later, see the answer below with over upvotes. This very problem hits ADO. NET DataTables too. So even that I can allow nulls in the backing field using this method, the DataTable won't let me store NULLs in a unique column in the first place. If anyone knows a solution for that, please post it here — dotNET.

Guys make sure you scroll down and read the answer with upvotes.