Oracle7 Server Utilities

Contents Index Home Previous Next

Importing Into Existing Tables

Manually Creating Tables before Importing Data

When you choose to create tables manually before importing data into them from an export file, you should either use the same table definition as was previously used or a compatible format. For example, while you can increase the width of columns and change their order, you cannot add NOT NULL columns or change the datatype of a column to an incompatible datatype (LONG to NUMBER, for example).

Disabling Referential Constraints

In the normal import order, constraints are imported only after all tables are imported. This sequence prevents the errors that could occur if a referential integrity constraint existed for data that has not yet been imported.

These errors can still occur when data is loaded into existing tables, however. For example, if table EMP has a referential integrity constraint on the MGR column that verifies the manager number exists in EMP, then a perfectly legitimate employee row might fail the referential integrity constraint if the manager's row has not yet been imported. When such an error occurs, Import generates an error message, bypasses the failed row, and continues importing other rows in the table. You can disable constraints manually to avoid this.

Similarly, a referential check from table AEMP into table BDEPT using DEPTNO would cause rows to fail, because the tables are loaded in alphabetic order, and rows from AEMP would be loaded before the corresponding rows in BDEPT.

To prevent errors like these, it is a good idea to disable referential integrity constraints when importing data into existing tables.

Manually Ordering the Import

When the constraints are re-enabled after importing, the entire table is checked, which may take a long time for a large table. If the time required for that check is too long, it may be beneficial to order the import manually.

To do so, do several imports from an export file instead of one. First, import tables that are the targets of referential checks, before importing the tables that reference them. Provided tables do not reference each other in circular fashion, and provided a table does not reference itself, this option works.


Contents Index Home Previous Next