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"
