Sometimes Check Constraints with Subqueries Do Not Work
Unlike triggers, check constraints are always invoked whenever a modification occurs. SQL Server does not support having a check constraint run a subquery that verifies that the
manager is an employee:
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.