Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 656
  • Last Modified:

Index, but Field value must be unique or Null

I am working on a database that synchronizes records in a table "person" from another database, but also has records that are not imported. So I created a field in my table which holds the id of the record in the other database, I need to be sure which records correspondences with which record in the other database.

The value of this field should be either be null (no corresponding record in the other database) or unique: there should not be two of the same values. But I can not create a unique index, because then SQL Server complains about several records having value Null.

How can I solve this?

  • 2
1 Solution
you could create a computed column on the table and then a unique index across your column and that e.g.

CREATE TABLE [dbo].[aaa] (
      [a] [int] IDENTITY (1, 1) NOT NULL ,
      [b] [int] NULL ,
      [c] AS (case when ([b] is null) then [a] else [b] end)

create unique index aaabbb on xxx (b,c)

so that B must be unique or null

the other way to go would be to have a non unique index on B and ensure uniqueness via a trigger
sybeAuthor Commented:
I have created a trigger. Thanks. All I wanted to know if there was something like a special unique index that would ignore Null values.
no unfortunately MS SQL Server doesn't allow UNIQUE WHERE NOT NULL Indexes like DB2/UDB

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now