Re: finding data violating constraint

On Tue, 19 Oct 2004, Keith Worthington wrote:

> I am trying to build a foreign key constraint. The objective is to prevent
> parts from being entered that do not have a valid sales account. When I try
> to build the constraint it fails telling me that there is a violation. I have
> looked at the data and just can't seem to find the problem. Is there a way to
> find the data that is causing the problem? I tried deleting all the data and
> building the constraint. That of course works but then I am unable to load
> the data and I have no better idea of where the problem is located.

What version are you using? IIRC, 7.4 should give at least the first
failing row in the error message.

In general you can use something like:

select * from referencing_table left outer join referenced_table on
(referencing_table.referencing_col = referenced_table.referenced_col)
where referenced_table.referenced_col is null;

to find unsatisfied constraint values. If the constraint has multiple
columns, you can add AND ... conditions inside the on clause (but the
where clause should be fine with just one column).