CODEWITHSUNDEEP
sqltriggersbusiness-logic
CodingGorilla
CodingGorilla

Reputation: 19842

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?

Upvotes: 0

Views: 681

Answers (2)

FuzzyTree
FuzzyTree

Reputation: 32402

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

Upvotes: 1

Fernando
Fernando

Reputation: 309

A (not too elegant) workaround for this would be to have a numeric field {activity_level} so {EmployeeNumber, activity_level} is unique. Then you should define MAX_INTEGER, or 0, or MIN_INTEGER or whatever value you want as the active-flag (employees with this value are active, all other are not). I know it is not very clean, but it could do the trick if you don't find anything better...

Upvotes: 1

Related Questions