5

I have a business requirement to have a FK field on TableA referencing TableB. If there is a value in TableBID, it should be unique, but it's not mandatory, so there could be multiple records with null values.

Is it possible somehow to enforce this type of uniqueness on the DB level, using either an index or a constraint?

9

You can create a unique filtered index as below.

CREATE UNIQUE NONCLUSTERED INDEX IX ON TableA(TableBID) WHERE TableBID IS NOT NULL

For versions of SQL Server prior to filtered index support (pre 2008) see this related question.

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.