A justification for referential integrity constraints

Yes, I know what you’re thinking.

“Here we go again.  Another database dude about to get stuck into us telling us about the importance of foreign keys for data integrity”

and yes, I could easily do that.  But you’ve seen that before (and by the way, it’s of course still totally valid – you should have all those integrity constraints defined!)

But here’s another reason, albeit a little tongue in cheek Smile

First I create a simple user with a couple of tables.


SQL> drop user demo1 cascade;

User dropped.

SQL>
SQL> grant create session, create table to demo1 identified by demo1;

Grant succeeded.

SQL>
SQL> alter user demo1 quota unlimited on users;

User altered.

SQL>
SQL> create table demo1.t1 ( x int, y int, constraint pk primary key ( x ) ) ;

Table created.

SQL>
SQL> create table demo1.t2 ( x int, y int) ;

Table created.

SQL>
SQL> alter table demo1.t2 add constraint pk2 primary key ( x );

Table altered.

SQL>
SQL> insert into demo1.t1 values (1,1);

1 row created.

SQL> insert into demo1.t1 values (2,2);

1 row created.

SQL> commit;

Commit complete.


And now, I take a datapump export of that schema


expdp compression=all dumpfile=DATA_PUMP_DIR:demo1.dmp logfile=DATA_PUMP_DIR:demo1.log schemas=demo1

Export: Release 12.1.0.2.0 - Production on Mon Nov 23 08:51:44 2015

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_01":  sys/******** AS SYSDBA compression=all dumpfile=DATA_PUMP_DIR:demo1.dmp logfile=DATA_PUMP_DIR:demo1.
log schemas=demo1
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
. . exported "DEMO1"."T1"                                4.804 KB       2 rows
. . exported "DEMO1"."T2"                                    0 KB       0 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
  C:\ORACLE\ADMIN\NP12\DPDUMP\DEMO1.DMP
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Mon Nov 23 08:52:10 2015 elapsed 0 00:00:23

Now I want to clone that schema to another, but in order to do it as efficiently as possible, I’m going to exclude indexes and constraints (they will be added later).


impdp directory=DATA_PUMP_DIR exclude=index exclude=constraint exclude=ref_constraint exclude=index_statistics exclude=table_statistics content=all dumpfile=demo1.dmp logfile=demo1.implog remap_schema=demo1:demo2 
Import: Release 12.1.0.2.0 - Production on Mon Nov 23 09:05:36 2015

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Username: /as sysdba

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
ORA-39002: invalid operation
ORA-39168: Object path REF_CONSTRAINT was not found.

Well…this is a disaster!  Because I had no referential integrity constraints, my import failed to find any…and hence could not exclude them!  This is actually documented as expected behaviour (Datapump Import Fails With ORA-39168, Doc ID 430702.1) and is done as a safety mechanism – if the datapump file was corrupt, we don’t want to be half way through importing a file and find bits and pieces misssing, so the checks are done up front and the import abandoned.

So the moral of the story is … make sure you’ve got referential integrity constraints Smile

4 thoughts on “A justification for referential integrity constraints

  1. Is there a specific reason you are SYS to export data ?
    I don’t think i’s a good practice to use SYS to export data first because SYS is a “special” account that should be used mainly for startup, shutdown, upgrade, backup, restore (or better use SYSBACKUP in 12c) second because it some old Oracle version (like 10.2) SYS cannot export data in a consistent way.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s