I\'m looking for a data integrity checking tool that lets me specify rules with
ID: 659340 • Letter: I
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
Explanation / Answer
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.
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.