I have an old Access table which is being migrated to SQL Server, and in it there's a field called "LinkedID". This field was mostly unused until ~5 years ago, so for many historical records it is either null or defaulted to "00000000". However, going forward, we're wanting it to be set up that no duplicate values are allowed (lets say 1,000 records are null, 500 are "00000000", and ~10,000 are actual unique values we want).

Looking into it, since I have multiple records which are set to "00000000" and multiple which are null, I understand that I can't use the Unique constraint. Is there a way that I can create a Check constraint that checks if the value being inserted/updated is unique if not all 0's or null? Or if it's not workable via a check constraint, my other thought was maybe use an insert/update trigger to somehow validate the data?

  • 1
    Is LinkedID a varchar column? Commented Oct 30, 2017 at 16:04
  • 1
    Suggestion - if "00000000" and NULL both mean there's no linked ID, it would make sense to use one consistent value (I'd go for NULL). Obviously, if they have different meanings to your application, you must retain both.
    – RDFozz
    Commented Oct 30, 2017 at 16:18
  • LinkedID is a varchar column. And very good idea with the unifying to a consistent value for old records, I'll bring this up with some of the other managers and hopefully set up one new standard for old/invalid values. Thanks!
    – Sean K.
    Commented Oct 30, 2017 at 18:36

1 Answer 1


You can use a filtered unique index for this:

CREATE UNIQUE INDEX LinkedID_Unique_except_Zeroes_or_Null 
    ON dbo.oldAccessTable (LinkedID)  
    WHERE LinkedID <> '00000000' ;  

The condition (LinkedID <> '00000000') takes care of both requirements, i.e. rows with '00000000' or NULL are ignored and not stored in the index, so they are not checked for uniqueness.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.