6

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? – ypercubeᵀᴹ Oct 30 '17 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 Oct 30 '17 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. Oct 30 '17 at 18:36
11

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.

  • 1
    Oh wow, I had no idea you could filter indexes like that! Thanks so much for your help! – Sean K. Oct 30 '17 at 18:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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