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?