Wednesday, February 10, 2010

SQL Checks And Rules

In most data models, there are columns that should take values only from a limited set.  For example, a state of License (as in our current project) may only take three values: AppliedFor, Granted or Denied. This kind of restriction is part of what is called Domain Level Integrity. How do you implement it?

Old Way: Separate Table
I used to create a separate table for the allowed values. It can be done, but this table has only couple records in it, is rarely updated and using a whole table for this is almost an overkill. Also, you end up writing more code to manipulate it.

New Way: SQL Rules
There is an SQL construct purpose of which is exactly this kind of constraint: SQL Rule (for Sybase docs: see this). An example:

create rule pub_idrule 
as @pub_id in ("1389", "0736", "0877", "1622", "1756")
or @pub_id like "99[0-9][0-9]" 

No comments: