hey All,
(Resend from novice)
I if a problem that apparently I can insert a record into my table
with a constrain while in fact the reference doesn't exist:
On the table acc_ops.tbl_part_status I have the following constrain
added:
CONSTRAINT fk_tbl_part_status_2 FOREIGN KEY (part_num)
REFERENCES acc_mkt.tbl_part_numbers (part_num) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
Now I seems to have records in the table acc_ops.tbl_part_status
twhere the part_num does not exists in the table
acc_mkt.tbl_part_numbers
This is my query to test :
SELECT * FROM acc_ops.tbl_part_status WHERE part_num NOT IN (SELECT
part_num FROM acc_mkt.tbl_part_numbers)
The above SQL returns me 2 records.
I don't allow nulls in both of my tables for the part_num field name.
I am a bit puzzled by this, or I must be blind, would the above
constraint not allow that?
Currently I am not be-able to make a test case because data is loaded
from JasperETL from a CSV file in a 18 step upload phase,
but I am working on it to start pin-pointing this.
One other 'proof' I have is that a pg_dump / pg_restore fails on the
exact same table and thus the restore of the DB fails.
Is the a option/setting in PostgreSQL that would allow such a insert
in table acc_ops.tbl_part_status that would invalidate the constrain?
(I Highly doubt that, just wondering how such a thing could happen....)
We are going to upgrade soon to 8.3.5 to see if the problem persists.
Ries
