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).
>

Stephan,

That query is exactly what I needed.  I ran it and out popped the two
offending records.  They had no values in a column that does not allow null!
Now I have to figure out what copy is doing that I end up with that condition.
}:-|  Thanks for the help.

BTW I am running PostgreSQL 7.3.6 with pgAdmin3 v1.0.2 on RedHat Enterprise
Linux v3.  I am going to investigate upgrading to at least 7.4.5.  Maybe even
8.0 Beta3 since the system is under development anyway.

Kind Regards,
Keith

______________________________________________
99main Internet Services http://www.99main.com