Thread: finding data violating constraint
Hi All, 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. Kind Regards, Keith ______________________________________________ 99main Internet Services http://www.99main.com
On Tue, 2004-10-19 at 14:04, 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. You need to show the definitions for the tables involved, how you are inserting your data and the exact text of error messages. Your description is far too general for us to help you much. If you add a constraint after the table is created, the existing data must satisfy the constraint. If you add the data after the constraint, it clearly must be added in the correct order; the sales account data would need to be inserted first. When you load the dependant table's data, PostgreSQL ought to tell you which data it is objecting to. -- Oliver Elphick olly@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA ======================================== "Commit thy way unto the LORD; trust also in him and he shall bring it to pass." Psalms 37:5
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).
"Keith Worthington" <keithw@narrowpathinc.com> writes:
> 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?
Use a more recent version of Postgres?
In 7.4 I get something like
regression=# alter table bar add foreign key (f1) references foo;
ERROR: insert or update on table "bar" violates foreign key constraint "$1"
DETAIL: Key (f1)=(2) is not present in table "foo".
regards, tom lane
> 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