The scope and applicability of integrity constraints specified by the SQL standard and provided by commercial and open source SQL DBMS implementations is pitiful:
- NOT NULL for single columns
- CHECK for single rows or tables
- Foreign key
There is no provision for constraints:
- on cardinalities (except for ‘one’ or ‘zero or one’)
- involving more than one table (except for foreign key constraints)
- on collections of rows identified by some common partial (probably foreign) key – e.g. the sum of percentage for all rows grouped by foo and bar must equal 100
- of a generally richer nature
Furthermore, some DBMS implementations check constraints either after every statement or when a transaction is committed. The former requires that some constraints have to be abandoned because it is too restrictive. The latter, although workable, means that it is often difficult to work out where in a transaction a problem occurred because it is too loose. In any case, the association of the transaction with the unit of validation indicates wooly thinking; there should be provision for a collection of inserts / updates to occur during which constraint checking is suspended and at the end of which constraints are checked. EssSkewEll provides a mechanism for this.
Considering that many organisations believe that their data is their most important asset (apart from their people, of course) all of this is surprising.
EssSkewEll attempts to correct these deficiencies by allowing developers to express rich constraints expressed in SQL. These constraints are verified before committing transactions and after executing a batch (the latter is optional).
Note: A significant amount of constraint checking may cause your application to run slowly and it can also introduce deadlocks. You might want to use EssSkewEll during testing but remove it when you deploy to production. Some might say that this is equivalent to wearing a life jacket when you are in dry-dock and removing it when you go to sea. You decide. Of course, all of the usual database tuning methods are available for improving constraint checking performance.
EssSkewEll is bytecode compatible with JDK1.4.
EssSkewEll is a P6Spy module.
Download EssSkewEll build 200611102015 and read the instructions contained in the distribution.
EssSkewEll is being used on Lance’s current project and within hours located the causes of some long standing problems.
The reason we were unable to find one of these problems until now is that it involves integrity issues between several tables, some legacy code, integration with a legacy database, triggers and all sorts of other things. Although we could see the problematic data, we had no idea what was occurring to get the database into the incorrect state.
By installing EssSkewEll, writing a constraint (query) to find the problem and then running our automated tests, we found that one of our existing tests incidentally exposed the problem during the course of its run and then ‘corrected’ it again before it finished.
Very useful indeed. Try it.