i added a table to a pre-existing design which now serves as the
parent table. there are records in the child table which were inserted
before and after the parent table creation. in order to build all the
PK-FK relationships and not violate an integrity constraint, i
inserted all keys from the child table (where not in the parent table)
to the parent table. then, of course, enabled the constraints...
in my testing *prior* to inserting the missing keys to the parent
table, i discovered that after enabling the constraints between parent
and child tables, and restoring the data, there still exists keys in
the child table that are not in the parent table. how is this
possible! the constraints are in place, shouldn't those records have
been rejected when i restored the data?
i did the following to rebuild the constraints among all tables (first
i dumped the data):
pg_dump -a -x -Fc ctst_nola > test.dmp
pg_restore -a -l test.dmp > test.list
i reordered the tables in test.list so that the parent tables are
loaded before child tables. after recreating the tables with
constraints, i restored the data:
pg_restore -a -d ctst_test -L test.list test.dmp
when i looked at the data in the tables, all records were inserted
into the parent AND child tables. i thought that since the references
were now in place, that there would be a number of "rejected" records
in the child table because of a referential integrity violation.
so, how is it possible for keys to exist in the child table, which are
not in the parent table? is it because these keys were inserted prior
to the creation of the parent table?
pg_restore permits these inserts, but when i restored using pg_dump,
they were rejected. is pg_restore buggy?
the system that we are using is:
ctst_test=# \! uname -a
Linux 2.2.17 #11 SMP Tue Sep 26 12:01:03 EDT 2000 i686 unknown
ctst_test=# \! psql -V
psql (PostgreSQL) 7.2.3
contains support for: readline, history
Portions Copyright (c) 1996-2001, PostgreSQL Global Development Group
Portions Copyright (c) 1996, Regents of the University of California
Read the file COPYRIGHT or use the command \copyright to see the
usage and distribution terms.
thank you in advance!
girlyDBA