Обсуждение: Suspend Referential Integrity?
I'm trying to port a database from Oracle to PostgreSQL. I used a perl script, ora2pg to extract the info from Oracle. The table data was extracted in alphabetical order. When I attempt to load it, I get referential integrity violations (eg. I attempt to load CUSTOMER, but CUSTOMOER depends on the SOURCE table, which hasn't been loaded yet). Is there a way to temporarily suspend RI checking so I can load the data and then fix it later? ================================================================= | Jim Jarrett,Madison, WI 94 Passat GLX | | mailto:jarrett@rpa.net 81 Rabbit Convertible 16v | | | | Any problem can be solved with the proper application of | | Force, Heat, Chemicals, or Money. | ================================================================
On Tue, Aug 09, 2005 at 09:14:50AM -0400, Jim Jarrett wrote: > Is there a way to temporarily suspend RI checking so I can load the data and > then fix it later? You could drop and then re-create the foreign key constraints with ALTER TABLE, or you could create the constraints as DEFERRABLE and defer them during the load with SET CONSTRAINTS (or make them INITIALLY DEFERRED). http://www.postgresql.org/docs/8.0/static/sql-altertable.html http://www.postgresql.org/docs/8.0/static/sql-createtable.html http://www.postgresql.org/docs/8.0/static/sql-set-constraints.html -- Michael Fuhr
On 8/9/05 9:14 AM, "Jim Jarrett" <jarrett@rpa.net> wrote: > I'm trying to port a database from Oracle to PostgreSQL. I used a perl > script, > ora2pg to extract the info from Oracle. > > The table data was extracted in alphabetical order. When I attempt to load > it, > I get referential integrity violations (eg. I attempt to load CUSTOMER, but > CUSTOMOER depends on the SOURCE table, which hasn't been loaded yet). > > Is there a way to temporarily suspend RI checking so I can load the data and > then fix it later? Not on a "whole database" level, as far as I know. You could: 1) Make foreign keys deferrable and load related tables inside a transaction. 2) Drop foreign key constraints until loading is complete and then reinstitute them (each one must be done individually). 3) Make a separate "loader" script that does the loading into "loader" tables that have no foreign key constraints and then write a SQL script to build the database from the loaded tables. 4) Reorder the oracle dump.... I think all of these require a bit of work beyond something like "SET REFERENTIAL INTEGRITY OFF", but I would love to be corrected.... Sean