Data constraints are used to enforce data integrity, and employing constraints of some kind is an important part of application design. Many ColdFusion developers write code to enforce integrity - they validate form fields against databases (using a <CFQUERY> tag) before inserting them into tables; they write complex <CFIF> statements to check for valid values, and then convert data on the fly using lists of <CFSET> statements.

Once again, this is a task best left to the database. Every decent database allows you to define constraints. From simple rules that establish default values or restrict values to those in another table (foreign keys) to more complex rules that define sets of valid values under specific conditions.

Here's a practical example, and one I see asked repeatedly on the Allaire Developer's Forum. You're inserting a record into a table, and you want to save the current date and time along with the record in a timestamp field. Sure, you could play with the ColdFusion Now() and CreateODBCDateTime() functions, but why would you want to? All you need to do is instruct the database to use the current date and time if no explicit value is provided, and then provide no value!

So why is this validation better suited for the database level rather than at the application level? There are two primary reasons:
1. Performance - The database can process these rules far faster than your application will be able to.
2. Data integrity - More often than not, your code will not be the only application to access the data. Embedding rules at the database level ensures that all applications use the same set of rules rather than requiring you to re-create the rules in each application and run the risk of missing them in one, or making a mistake in them.
3. Use Stored Procedures

Stored procedures are blocks of SQL code that are stored on the database server - similar to custom functions (or ColdFusion Custom Tags). Using stored procedures, you can group sets of related SQL statements together as a single unit. Instead of calling each SQL statement individually and passing values retrieved in one statement down to the next, you can simply call a stored procedure that contains all the needed statements.

The fine points of stored-procedure use probably require their own column, but for now it's worth noting the primary advantages of using stored procedures:

ColdFusion fully supports the use of stored procedures. In ColdFusion 4.0 you now have two different methods with which to invoke stored procedures: as simple SQL passed to a <CFQUERY> tag, and via the new <CFSTOREDPROC> tag family. The detailed differences between the two are beyond the scope of this column, but Table 1 lists the basic advantages of each.

There is no hard and fast rule about where to use stored procedures and where not to, but here's one place to start: if you ever find yourself calling a <CFQUERY> and then passing the results of that query to another <CFQUERY> (perhaps using the ValueList() or QuotedValueList() functions), that code should probably be a stored procedure.

4. Be Trigger-Happy
All client/server databases support triggers. A trigger is simply a block of SQL code that is executed when specific events occur. Triggers are associated with specific operations on specific tables. For example, you might create a trigger that is called whenever a SQL INSERT or UPDATE operation is performed on a specific table.

An ideal use of triggers is data cleanup. For instance, if you want state abbreviations always inserted in uppercase you can write a simple trigger for INSERT and UPDATE operations that will replace the passed state abbreviation with an uppercase version.

Of course, not only will this be faster than doing data conversion at the ColdFusion level, but it also will allow all client applications (including ColdFusion) to share the same code - and it all happens transparently.

5. Schedule Tasks
Most databases allow you to schedule the execution of SQL statements. ColdFusion also allows you to schedule tasks by using <CFSCHEDULE> or the ColdFusion Administrator. The ColdFusion scheduling engine is very useful for scheduling execution of ColdFusion code, but it should never be used to schedule the execution of SQL statements themselves unless you need to do processing on the query results.

For example, if you need to execute daily SQL cleanup code (to perform database cleanup), or if you need to move data from a live table to an archive table on a regular basis, or if you need to run nightly calculations on one table and write the results to another - all of these are best handled by the database's own scheduling engine.

The rule here is to use ColdFusion scheduling to schedule ColdFusion processing and use database scheduling to schedule database processing. It makes absolutely no sense to tie up ColdFusion when it's the database doing the actual work.

Conclusion
The bottom line here is that database developers put a lot of time and effort into optimizing their products. Rather than reinvent the wheel, you should strive to take advantage of their work whenever possible. Most ColdFusion applications are running against powerful database servers that are not working as hard as they should. Push your database server - make it work as hard as it was designed to. Doing so will dramatically improve application performance - and it'll likely save you time too.