Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


advertisement
 

Avoid These Common Business Rule Implementation Mistakes : Page 3

Some solutions for implementing business rules can allow invalid data to get into the database. Find out where the loopholes lie in these all-too-common approaches.


advertisement

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.



Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap