Software for (logical) data integrity checks in database

Question

I'm looking for a data integrity checking tool that lets me specify rules with which it scans a database for logical inconsistencies.

Background:

We have a large application where the data integrity is maintained in the code: there are e.g. no triggers in the database for cascading deletes.
Because of things like program aborts, failing updates, errors in code, etc. data can become corrupt.

I would like to have a separate tool to check for logical errors/inconsistencies in the data, specifying rules like:

• Values in field must be filled in

• Field values are required

• Field values must be in constant range [X..Y]

• Field values must match a regexp

• All values for Field X must be unique

Or 'across table' relations:

• Field values must be in range specified by field X from table T to field Y from table T

• Values in field X of table T should come from field Y of table S

• Field X in table T must be larger than field Y in table T

I'm not talking about the programs that most DBMS already have and that check internal file structures, index corruption etc (e.g. the Check Database Integrity Task that you can use in a maintenance plan for SQL Server), I want to check for logical errors.

Requirements:

• Multiple DBMSs, I would just have to specify a database type/location/login. Firebird, MSSQL, Oracle are a must.

• Running under Windows

• Free would be nice

Answer 1

I would add constraints to your dba as said above. Of course, if your data doesn't conform to the constraint, then you'll have to with update the records to get the data in the correct form. Identifying the data that is inconsistent with business requirements is not enough. You have to fix the data dictionary with constraints and then add triggers. Build the complicated business requirements into the code.

Answer 2

I would suggest using Python and a simple script using the appropriate database interface:

#!/usr/bin/python

import MySQLdb

# Open database connection
db = MySQLdb.connect("localhost","testuser","test123","TESTDB" # You will need your real DB details here
                    )

# prepare a cursor object using cursor() method
cursor = db.cursor()

# Prepare SQL query to get records from the database.
sql = "SELECT * FROM EMPLOYEE"  # for example
try:
   # Execute the SQL command
   cursor.execute(sql)
   # Fetch a row in a list.
   for row in cursor.fetchone():
      # Put your checks here with Failure set to the results!
      if Failure:
          # Now print fetched result
          print "some details that identify the record that failed e.g.: %s, %s" % \
             (row[0], row[1])
except:
   print "Error: unable to fetch data"

# disconnect from server
db.close()

• Free
• Can interface to a number of database backends
• Cross Platform
• Your checks can be as simple or as complicated as you need.
• Lots of web help, books & examples
• Once you are happy it is working from your local machine it could be simply ported to the server and run nightly from a chron job.