One of the known problems in adding
constraints to any kind of database is the
validation of existing data. For this reason,
DBAs often create check, foreign-key, and
other types of constraints with the WITH
NOCHECK clause. That way, any rows of
data that violate the constraint are ignored.
When data validation is important, DBAs
must find all the constraint-violating rows so
that they can
fix them. To
find them,
they have to
use a different SELECT statement for
each constraint, which is a tedious task. For
example, suppose a table has these check and
foreign-key constraints:
ALTER TABLE emp ADD CONSTRAINT
ck_emp_sal CHECK
(salary BETWEEN 4000 and 10000)
ALTER TABLE emp ADD CONSTRAINT
FK_emp_mgr FOREIGN KEY
(mgr_id) REFERENCES emp (emp_id)
First, the DBA has to run the following
SELECT statement to retrieve the rows that
violate the check constraint:
SELECT * FROM emp WHERE
salary < 4000 OR salary > 10000
Then, the DBA has to run the following
SELECT statement to retrieve the rows that
violate the foreign-key constraint:
SELECT * FROM emp WHERE
mgr_id NOT IN
(SELECT emp_id FROM emp)
I wrote a procedure called showViolatingRows to automate the tedious task of
finding rows that violate check and foreignkey constraints. This procedure needs only
one piece of input: the constraint name that
uniquely identifies the table it's declared in.
As the excerpt in Listing 1 shows, the
showViolatingRows procedure dynamically activates the DBCC CHECKCONSTRAINTS statement. This statement
checks the integrity of a specific constraint
or all the constraints for a specified table. The
showViolatingRows procedure stores the DBCC CHECKCONSTRAINTS results
in a temporary table named ##dbcc.
The DBCC CHECKCONSTRAINTS
statement's results consist of three pieces of
information: the name of table, the name
of the constraint, and column values that
identify the rows violating the constraint.
You can use these values in a SELECT
statement's WHERE clause. Thus, for each
constraint-violating row, the showViolatingRows procedure uses a SELECT statement
to retrieve that row's data. The procedure
stores the results in a global temporary table
(##tempResults) that's returned to the
user at the end of the showViolatingRows
procedure's execution.
On the SQL Server Magazine Web site,
you'll find a file named showViolatingRows.sql, which includes the code for the showViolatingRows procedure as well as code
that demonstrates how you might use the
procedure in a script. I tested showViolatingRows on SQL Server 2005 Standard Edition and SQL Server 2000 Standard Edition
from a Windows XP client.
—Eli Leiba
End of Article
