Re: Data integrity and sanity check

> 2) re-check any constraint inserted into the database

There should not be any if it was accepted, however if it's a new
constraint it doesn't get applied to data that already exists.  A dump
and restore will ignore these as well (with good reason).

I suppose the easiest way to find if data violates current constraints
(rather than the constraints applied during initial insertion) is to:

update table set column = column;

That should re-process any constraints.


Primary keys, or other index style constraints (UNIQUE for example)
are always guarenteed.  The only way that new constraints are added is
via alter table commands.

BTW.  There are good reasons sometimes for having data that violates
current constraints.  The top of a tree may have a static record with
a null parent.  The NOT NULL constraint added after this entry (via
alter table add constraint) should not affect the static record, so
unless you know your data quite well this type of tool wouldn't be
particularly useful anyway.

Normally I use triggers which are programmed to account for that, but
there are a few cases where the check constraint speed (rather than
the trigger) is useful and the assumption the initial record will
never be touched is good enough.