Limit SQL Validation to a Two-Column Set

Question

Given the next column of the table. How can I guarantee that it activecan only be set to true if and only if the UserId is unique? note, orgId and UserId are the key to many, many relationships, and userId can also be a duplicate.

OrgId    int 
UserId   int
Active   bit

Example 1 is invalid because the user ID is displayed 2 times, and the active one is set to true two times.

orgId userId active
1      2       1
2      2       1   

Example: 2 valid.

orgId userId active
1      2       1
2      2       0
3      3       1

Answer 1

You want to make sure that no more than one value userIdmatters active = 1. You can do this with a filtered unique index:

create unique index idx_table_userId_active
    on table(userId)
    where active = 1;

Answer 2

trigger. Check , .