Search code examples
sqltriggersbusiness-logic

Most efficient way to enforce business rule on sql data

So I have a table of Employees that has the fields EmployeeId (GUID), EmployeeNumber (string), and IsActive (bit) among others. I would like the SQL Server to enforce a rule where any number of records can have the same employee number, but only one record of a given employee number may have the IsActive bit set at a time.

I'm trying to determine the most efficient or effective way to have the SQL server do this, but so far I've only come up with the idea of using a Trigger. Considering the trigger, I was thinking I would probably need to use a cursor to iterate each of the rows in the inserted table and check each row individually. And this has me worrying about performance.

I've considered using a constraint, but clearly I can't use a UNIQUE constraint because I'd only be allowed two records. Is there a better way to handle this rather than a trigger?

Solution

  • The links below explain how to enforce conditional unique constraints based on bit/boolean fields

    SQL Server - conditional unique constraint

    Oracle - Conditional unique constraint in oracle db

    Postgresql - PostgreSQL: Conditional unique constraint

    MySQL - Doesn't seem to be possible