Handling Advanced Database Features with Rails

Database-level constraints provide a way to explicitly specify an application's implicit assumptions about its data. There are two types of constraints, which should not be confused:

Business logic

"A manager may not manage more than five employees." The key characteristic of business logic constraints is that they could conceivably change throughout the lifetime of the database. Business logic constraints should never be in the database, barring a very good reason to the contrary.

Integrity

"U.S. Social Security numbers, when provided, must contain exactly nine digits." Integrity constraints define the nature of the data being represented. Admittedly, "the nature of the data" is a somewhat nebulous concept; the meaning will differ between databases. Integrity constraints must reside in the database, if for no other reason than to provide a last-level sanity check on the data.

As with any other area of data modeling, there are gray areas. An example would be "an employee's salary must be positive," which could conceivably go either way.* The advantage of constraints is that they narrow the domain of possible results the database can generate. When you know the DBMS for an online store can never output a negative price for a product, you can sum the prices for the line items belonging to an order without worrying about invalid prices. Though the line is drawn in different places for different applications, the basic principle is this: the database should not enforce business logic, but it should enforce consistency and integrity.

Regardless of differences of opinion on check constraints, one type of constraint is non-negotiable: foreign-key constraints. If a foreign-key relationship is required, an unassociated record is semantically meaningless and must not be allowed to happen. It only makes practical sense to formalize that association.

The only truly robust way to ensure that a database maintains integrity over years as it accumulates data (as databases tend to do) is to declare appropriate constraints on the data. Unless you can say for certain that every application or person accessing the database will do so through the domain model (going through all associated validations) every time, the only sensible option is to treat the database as an integration database.

There is a bonus to providing constraints: typically, the more constraints provided on a database, the better job the query optimizer can do at creating a query plan.

A common complaint about database constraints is that they require you to specify semantic information in two places: your database and your application code (you usually want to trap invalid data in your application's validations before attempting to insert it into your database, even if the database would catch the error anyway). The DrySQL library goes a long way toward removing this duplication. It infers the schema relationships and validation rules from the database's types and constraints, so they don't have to be specified in the application. DrySQL works with all of the major DBMSs: PostgreSQL 8 and up, MySQL 5 and up, SQL Server, Oracle, and DB2.

With DrySQL installed, you can simply require the library in the environment configuration file:

  require 'drysql'

Then, all that is needed is to inform ActiveRecord of the mapping between tables and model classes (even that is not necessary if the tables are named according to the defaults):

  class Client
    set_table_name "customers"
  end  

If the table had been named clients, you would not even need the set_table_name call. The relationships and constraints will be inferred from the customers table's constraints.