Index, but Field value must be unique or Null

Posted on 2006-05-02
Last Modified: 2012-05-05
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?

Question by:sybe
    LVL 50

    Accepted 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)
    ) ON [PRIMARY]

    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
    LVL 28

    Author Comment

    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.
    LVL 50

    Expert Comment

    no unfortunately MS SQL Server doesn't allow UNIQUE WHERE NOT NULL Indexes like DB2/UDB

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    I recently came across an interesting Question In EE ( and was puzzled about how to achieve that using SSIS out of the box tasks, which was i…
    When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
    Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now