Avoid These Common Business Rule Implementation Mistakes : Page 5

The Right Way: Use Referential Integrity

Let me show you the right way to implement this business rule. I will use referential integrity, for which I need to add another column (manager_status) and populate it:

alter table employee add manager_status Varchar(10)                 check(manager_status in('Employee'))
update employee set manager_status = 'Employee' 
  where manager_id is not null

Also, I will make sure that the new column can store only one not null value (Employee) or a Null:

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 steep—you 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.

Previous Page