Also, I will make sure that the new column can store only one not null value (
Employee) or a
alter table employee add constraint manager_status_populated check(
manager_id is null or manager_status is not null)
Now I will have a referential integrity (RI) constraint verify that the value in
manager_status matches the status of the manager:
alter table employee add constraint FKTarget unique(employee_id, status)
alter table employee add constraint manager_is_employee
foreign key(manager_id, manager_status)
references employee(employee_id, Status)
This actually works in all the scenarios described previously, although if you try to update
manager's status to Contractor, you'll receive a somewhat misleading error message:
Msg 3960, Level 16, State 2, Line 1
Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot
isolation to access table 'dbo.employee' directly or indirectly in database 'test1' to
update, delete, or insert the row that has been modified or deleted by another transaction.
Retry the transaction or change the isolation level for the update/delete statement.
This RI constraint will work no matter what, always protecting your data. Yet the price tag for unbreakable integrity is steepyou need to add a column and indexes.
Awareness Leads to Wise Decisions
Different businesses have different needs. Sometimes even if you are fully aware that your solution has loopholes, you still might have reasons to go for it. Just be aware of the all-too-common problems demonstrated here.