How does Oracle Data Pump Import handles referential integrety constraint violations?

How does Oracle Data Pump Import handles referential integrety constraint violations?

To find out, let's use the two tables SHIPS and PORTS, which are connected by a referential constraint from SHIPS to PORTS, called FK_SHIPS_PORTS.

CREATE TABLE SHIPS
(
  SHIP_ID       NUMBER,
  SHIP_NAME     VARCHAR2(20 BYTE),
  CAPACITY      NUMBER,
  LENGTH        NUMBER,
  HOME_PORT_ID  NUMBER,
  LIFEBOATS     NUMBER(3),
  CONSTRAINT PK_SHIPS PRIMARY KEY (SHIP_ID),
  CONSTRAINT FK_SHIPS_PORTS FOREIGN KEY (HOME_PORT_ID) 
  REFERENCES PORTS (PORT_ID)
);

CREATE TABLE PORTS
(
  PORT_ID    NUMBER,
  PORT_NAME  VARCHAR2(20 BYTE),
  COUNTRY    VARCHAR2(40 BYTE),
  CAPACITY   NUMBER,
  CONSTRAINT PK_PORTS PRIMARY KEY (PORT_ID)
);

In other words, you cannot add a ship without a valid PORT_ID that already exist in table PORTS.

PORT_ID	PORT_NAME	COUNTRY	CAPACITY
1	Baltimore	USA
2	Charleston	USA
3	Tampa	USA
4	Miami	USA

SHIP_ID	SHIP_NAME	CAPACITY	LENGTH	HOME_PORT_ID	LIFEBOATS
1	Codd Crystal	2052	855	1	80
15	Codd Champion	2000	650		30
2	Codd Elegance	2974	952	2	95
16	Codd Victorious	2055	876	2
17	Codd Grandeur	2030	840	4
18	Codd Prince	1500	550	2	32
20	Codd Norway	1500	900	3	80

I now remove one row from the PORTS table, so that new rows being imported will have a missing parent key. In order to do that I need to also remove any child record from SHIPS, in my case, only one:

DELETE TESTUSER.SHIPS WHERE HOME_PORT_ID = 1;
DELETE TESTUSER.PORTS WHERE PORT_ID = 1;
COMMIT;

1 row deleted.
1 row deleted.
Commit complete.

SCENARIO 1: "What happens if we use TABLE_EXISTS_ACTION=REPLACE when the table being replaced has a referential constraint to another table?"

Result:

• The operation will report an error because of a constraint violation issue.
• All rows are imported into SHIPS, including the offending ones
• The referential constraint is dropped.

Error message:
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "TESTUSER"."SHIPS" 7.289 KB 7 rows
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
ORA-39083: Object type REF_CONSTRAINT failed to create with error:
ORA-02298: cannot validate (TESTUSER.FK_SHIPS_PORTS) - parent keys not found
Failing sql is:
ALTER TABLE "TESTUSER"."SHIPS" ADD CONSTRAINT "FK_SHIPS_PORTS" FOREIGN KEY ("HOME_PORT_ID") REFERENCES "TESTUSER"."PORTS" ("PORT_ID") DEFERRABLE ENABLE


SCENARIO 2: "What happens if we use TABLE_EXISTS_ACTION=TRUNCATE when the table being truncated has a referential constraint to another table?"

Result:

• The operation will report an error because of a constraint violation issue.
• The SHIPS table is left truncated, no rows are loaded.
• The referential constraint stays.

Error message:
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39153: Table "TESTUSER"."SHIPS" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped due to table_exists_action of truncate
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
ORA-31693: Table data object "TESTUSER"."SHIPS" failed to load/unload and is being skipped due to error:
ORA-02291: integrity constraint (TESTUSER.FK_SHIPS_PORTS) violated - parent key not found


Both cases requires manual intervention afterwards to cleanup offending rows, and then to recreate the constraint.
One way to do so would be to allow for orphan rows through temporarily disabling the index, loading the rows, then reenabling the index using the NOVALIDATE option.

First disable the constraint:
SQL> ALTER TABLE SHIPS DISABLE CONSTRAINT FK_SHIPS_PORTS;

Table altered.

Load the data once more:
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39153: Table "TESTUSER"."SHIPS" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped due to table_exists_action of truncate
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "TESTUSER"."SHIPS" 7.289 KB 7 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Finally, enable the constraint again. However, tell oracle to disregard existing rows, only apply the constraint to new rows:
SQL> ALTER TABLE SHIPS ENABLE NOVALIDATE CONSTRAINT FK_SHIPS_PORTS;

Table altered.

SQL>

Example is based on Steve O'Heam's "SQL Certified SQL Expert Exam Guide"