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


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

On Tue, 2004-10-19 at 14:04, Keith Worthington wrote:
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 (AT) lfix (DOT) 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


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

On Tue, 19 Oct 2004, Keith Worthington wrote:

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


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

"Keith Worthington" <keithw (AT) narrowpathinc (DOT) com> writes:
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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

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


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly