On Mon, Jun 20, 2005 at 08:18:00AM -0400, John Burger wrote: > > I don't have personal experience with this, but I thought it was fairly > common to DEFER constraint checking until after a restore completed, > for exactly this reason.
Regarding DEFERRABLE, according to the CREATE TABLE documentation, "Only foreign key constraints currently accept this clause. All other constraint types are not deferrable." http://www.postgresql.org/docs/8.0/static/sql-createtable.html pg_dump could defer adding CHECK constraints until after data is restored, but currently it doesn't behave that way -- it usually emits CHECK constraints with the CREATE TABLE statements, and I don't see an option in the documentation to change that behavior. Search the list archives for past discussion; for example: http://archives.postgresql.org/pgsql-hackers/2004-03/msg00035.php http://archives.postgresql.org/pgsql-hackers/2004-03/msg00755.php It should be possible to use a trigger instead of a CHECK constraint because pg_dump adds triggers after data is restored. But without careful design you still have the same integrity problem as with a CHECK constraint: data is checked when it's inserted or updated, but not when the referred-to data is modified. For that you'd need another constraint or trigger that knows how to check all the dependent data. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org
