That may lead to non-unique values - consider where dupNulls. I see no duplicate value. For older version of SQL Server, a possible alternative to a trigger involves a computed column: Create a computed column which uses the value of your "unique" column if it's not NULL , otherwise it uses the value of the row's Primary Key column or any column which will be unique.
Paul Turner Paul Turner Very interesting trick the use of a computed column. There could be a collision problem between the PK and the actual field value, but with some prefixing it should work. You can create a view in which you select only not null values and create an index on it.
Svetlozar Angelov Svetlozar Angelov The Overflow Blog. Podcast Making Agile work for data science. Stack Gives Back Featured on Meta. New post summary designs on greatest hits now, everywhere else eventually.
Visit chat. Linked Related 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. Remove From My Forums. Answered by:. Archived Forums. Sign in to vote. On Unique Index you can see the problem.
This sample works on others Databases!! To move you need to remove the Unique Index and make test duplicates by Code! Tuesday, November 24, PM. Although this command option is not an ANSI standard, it solves my problem.
Wednesday, November 25, PM. Please see the BOL entry on this as it applies to constraints, indexes, and working with nulls. Hope this helps, Sean. I know about SQL Server!!!
0コメント