Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 266
  • Last Modified:

Unique index dependent on a column


Suppose I have the following table in SQL Server:
Name     IsRetired
------      -----------
John       0
Joe         0
John       0
John       1

I want to make Name unique IF IsRetired is 1.  So the example above is fine because John appears only once when IsRetired is 1.  However, I couldn't add another John and set IsRetired to 1.

is there a way to create a unique index for this?
1 Solution
Shaun KlineLead Software EngineerCommented:
I do not believe unique indexes can be partially unique. In your example, you have two John's with a IsRetired = 0.

You could handle this through a Insert/Update trigger, however that starts to embed business logic into the trigger, where it probably should not be.
sfun28Author Commented:
agreed.  thanks so much

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now