Image of Navigational Map linked to Home / Contents / Search Client Weight Loss

by Lisa Hooper - GUI Computing
Image of Line Break

The simple action of adding a single record to a database table can often involve a large number of validation routines before an update is even attempted. These validation routines can often involve querying other database tables and checking for correct data types, which can lead to unnecessary processing time and cluttered code if we implement it on the client side (e.g. in VB). Not to mention the maintenance and deployment problems it can create when changes are required.

In these days of thin clients and the move towards browser-based database access (for good or ill), it's a brave programmer who would assume that her client application was the correct place for validation. With some forward planning and careful thought during database design, SQL Server errors can be used to effectively reduce this additional processing and code clutter and bring business rule implementation back where it belongs - which is anywhere but the client.

We all pay lip service to this style of thinking and programming, but few realise that with a just few simple techniques we can get SQL Server to do a lot of the work - with less code and better performance.

Validating Values Against Other Tables

Often adding records to a table will require data in a particular column to exist in another table. A common example might be 'an employee must belong to a valid department'. Visual Basic code to deal with this rule might be:

If txtDepartmentCode <> "" then
Set ss = db.createsnapshot("Select * from Departments Where 
DepartmentCode = '" & txtDepartmentCode & "'", dbSqlPassthrough)
If ss.recordcount = 0 then
	ss.close
	MsgBox "The department you have entered is not valid"
	Exit Sub
Endif
ss.close
	Endif
On error resume next
	db.execute "Insert Into Employees (EmpCode, EmpName, DeptCode) 
        Values ('" & TxtEmpCode & "', '" & txtEmpName & "', '" & 
        txtDepartmentCode & "')", dbSqlPassthrough
'Process errors next

Although this method does the job we can achieve the same result with less code and without having to perform the additional query at the beginning.

Start by creating a relationship between the two tables by defining a foreign key on the Employees table:

	CREATE TABLE Employees
                (EmpID varchar (15) NOT NULL ,
		EmpName varchar (50) NULL ,
	        DeptCode int NULL , 
	CONSTRAINT PK_Employees PRIMARY KEY  CLUSTERED 
		(EmpID	),
	CONSTRAINT FK_Employees_Departments FOREIGN KEY 
		(DeptCode)
                REFERENCES dbo.Departments (DeptCode))

Once you have created this relationship SQL Server will use its own internal mechanisms to enforce it. Hence if you attempt to insert a record into the employees table where the department does not exist in the departments table a SQL Server error is generated. So instead of querying the departments table ourselves why not just send the data and check for the specific error.

On error resume next
db.execute "Insert Into Employees (EmpCode, EmpName, DeptCode) 
Values ('" & TxtEmpCode & "', '" & txtEmpName & "', 
'" & txtDepartmentCode & "')", dbSqlPassthrough
If err <> 0 then
	If instr(error, "Violation of FOREIGN KEY constraint 
	'FK_Employees_Departments'") then
		MsgBox. "The department you have entered is not valid"
		Exit Sub
	Else
		GlbErrorHandler Err
	Endif
Endif

Obviously the benefits of this method multiply when there are more tables to validate against.

Checking For Duplicate Primary Keys

In many applications the primary key for a table will often consist of a user entered code ie customer code. When a user enters a code it will usually need validation against codes already entered into the table. A primary key check in our first code example might look like this:

If txtDepartmentCode <> "" then
	Set ss = db.createsnapshot("Select * from Departments Where 
	DepartmentCode = '" & txtDepartmentCode & "'", dbSqlPassthrough)
	If ss.recordcount = 0 then
		ss.close
		MsgBox "The department you have entered is not valid"
		Exit Sub
	Endif
	ss.close
Endif
If txtEmpCode <> "" then
	Set ss = db.createsnapshot("Select * from Employees Where 
	EmpCode = '" & txtEmpCode & "'", dbSqlPassthrough)
	If ss.recordcount <> 0 then
		ss.close
		MsgBox "The Employee code you have entered already exists"
		Exit Sub
	Endif
	ss.close
Endif
On error resume next
db.execute "Insert Into Employees (EmpCode, EmpName, DeptCode) 
Values ('" & TxtEmpCode & "', '" & txtEmpName & "', 
'" & txtDepartmentCode & "')", dbSqlPassthrough
'Process errors next

As you can see we now have to perform two separate queries before the insert is attempted and our code is growing rapidly. Now as we already have a primary key defined on our employee table, it is a lot simpler to allow SQL Server to do its job. This can be demonstrated by the small addition to the second code example:

On error resume next
db.execute "Insert Into Employees (EmpCode, EmpName, DeptCode) 
Values ('" & TxtEmpCode & "', '" & txtEmpName & "', 
'" & txtDepartmentCode & "')", dbSqlPassthrough
If err <> 0 then
	If instr(error, "Violation of PRIMARY KEY constraint 'PK_Employees'") 
	then   	MsgBox. "The Employee code you have entered already exists"
		Exit Sub
	else
		If instr(error, "Violation of FOREIGN KEY constraint
		'FK_Employees_Departments'") then
			MsgBox. "The department you have entered is not valid"
			Exit Sub
		Else
			GlbErrorHandler Err
		Endif
	Endif
Endif

Other Validations

Business rules are often excellent server side validation candidates. These rules would usually be enforced by SQL Server CHECK constraints. For example if all employee codes must begin with the letter 'E' the following check constraint might be created:

	ALTER TABLE Employees
	ADD
	CONSTRAINT CC_Employees_EmpCode CHECK (EmpCode LIKE 'E%')

Again it would just be a matter of checking for the name of the constraint in the error and displaying the appropriate error message to the user.

The more complex a database the more the benefits of server side validation become apparent. Storing data validation rules in the database rather than in any code that accesses that database is obviously the preferable method. Databases were built to enforce data rules so let's not do their job for them.



Written by: Lisa Hooper
March '97


Image of Line Break
[HOME] [TABLE OF CONTENTS] [SEARCH]