Avoid These Common Business Rule Implementation Mistakes : Page 3

Sometimes Check Constraints with Subqueries Do Not Work

alter table employee add constraint manager_is_employee check(
  manager_id is null or
(select count(*) from employee e where e.employee_id = manager_id
    and e.status = 'Employee') = 1
)

Msg 1046, Level 15, State 1, Line 3
Subqueries are not allowed in this context. Only scalar expressions are 
allowed.

However, there is a very easy and well-known workaround. You can wrap the subquery in UDF:

create function check_manager_status(@manager_id int)
returns tinyint
as
begin
  declare @ret tinyint
  set @ret = (select count(*) from employee e where e.employee_id = 
@manager_id
    and e.status = 'Employee')
  return @ret
end
go

And invoke the UDF in a check constraint:

alter table employee add constraint manager_is_employee check(
  manager_id is null or
dbo.check_manager_status(manager_id) = 1
)

The check constraint created all right, and it looks like it is working. These four rows insert successfully, as they should:

delete from employee
insert into employee(employee_id, first_name, last_name, manager_id, status)
  values(1, 'Jane', 'Wilson', null, 'Employee')
insert into employee(employee_id, first_name, last_name, manager_id, status)
  values(2, 'Sue', 'Smith', 1, 'Contractor')
insert into employee(employee_id, first_name, last_name, manager_id, status)
  values(3, 'Sam', 'Brown', 1, 'Employee')
insert into employee(employee_id, first_name, last_name, manager_id, status)
  values(4, 'Jill', 'Larsen', 3, 'Employee')

But an attempt to insert anybody who reports to a contractor fails, as it should:

insert into employee(employee_id, first_name, last_name, manager_id, status)
  values(5, 'Jack', 'Hansen', 2, 'Contractor')

Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the CHECK constraint 
"manager_is_employee". The conflict occurred in database "test1", table "dbo.employee", column 'manager_id'.
The statement has been terminated.

However, this all-too-common solution has a loophole. Suppose Sam Brown is no longer an employee; suppose he has become a contractor:

update employee set status = 'Contractor' where employee_id = 3

Unfortunately, the update successfully completes, and you have a person (Jill Larsen) reporting to a contractor in your database. Your business rule has been violated and you know nothing about it! It's time to restore the integrity of your data:

update employee set status = 'Employee' where employee_id = 3

And try to fix the problem by creating another check constraint, very similar to the first one:

create function number_of_employees(@manager_id int)
returns int
as
begin
  declare @ret int
  set @ret = (select count(*) from employee e where e.manager_id = 
@manager_id
    and e.status = 'Employee')
  return @ret
end
go
alter table employee add constraint contractor_has_no_employees check(
  status = 'Employee' or
dbo.number_of_employees(employee_id) = 0
)

Suppose you try to assign contractor status to Sam Brown again. Apparently, the new constraint protects the integrity of your data:

update employee set status = 'Contractor' where employee_id = 3

Msg 547, Level 16, State 0, Line 1
The UPDATE statement conflicted with the CHECK constraint 
"contractor_has_no_employees". The conflict occurred in database "test1", table "dbo.employee".
The statement has been terminated.