Getting Referential Constraints using DBMS_METADATA

The GET_DDL function of the DBMS_METADATA package, supplied by Oracle, is a nice tool to extract the DDLs of database objects. This quick guide will show you how to deal with the problem of exporting referential constraints – what problem, I hear you saying?

Let’s take a look at the example below. We’ll work with the following tables:

CREATE TABLE dept (
  dept_id NUMBER CONSTRAINT pk_dept PRIMARY KEY,
  name VARCHAR2(20)
);

CREATE TABLE emp (
  emp_id NUMBER CONSTRAINT pk_emp PRIMARY KEY,
  name VARCHAR2(20),
  mgr_id NUMBER CONSTRAINT fk_emp_mgr_id REFERENCES emp(emp_id),
  dept_id NUMBER CONSTRAINT fk_emp_dept_id REFERENCES dept(dept_id)
);

As you may see, the EMP table has two referential constraints – to the DEPT table, and to itself. Now, let’s see the DBMS_METADATA package in action.

Firstly, let’s set two transform parameters:

• SQLTERMINATOR to true – just to get the semicolon at the end of each command and
• SEGMENT_ATTRIBUTES to false – to shorten the generated DDLs for the purpose of this example.

Here we go:

BEGIN
  DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.session_transform,
                                    'SQLTERMINATOR', true);
  DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.session_transform,
                                    'SEGMENT_ATTRIBUTES', false);
END;
/

Secondly, we generate the DDLs:

SELECT dbms_metadata.get_ddl('TABLE', table_name)
  FROM user_tables
WHERE table_name in ('DEPT', 'EMP');

Which results in:

CREATE TABLE "HR"."DEPT" 
("DEPT_ID" NUMBER, 
 "NAME" VARCHAR2(20), 
 CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPT_ID") ENABLE
);

CREATE TABLE "HR"."EMP" 
("EMP_ID" NUMBER, 
 "NAME" VARCHAR2(20), 
 "MGR_ID" NUMBER, 
 "DEPT_ID" NUMBER, 
 CONSTRAINT "PK_EMP" PRIMARY KEY ("EMP_ID") ENABLE, 
 CONSTRAINT "FK_EMP_MGR_ID" FOREIGN KEY ("MGR_ID")
  REFERENCES "HR"."EMP" ("EMP_ID") ENABLE, 
 CONSTRAINT "FK_EMP_DEPT_ID" FOREIGN KEY ("DEPT_ID")
  REFERENCES "HR"."DEPT" ("DEPT_ID") ENABLE
);

Now, do you see the problem here? What if we had tens of tables and many referential constraints? If you would try to recreate all the tables, and do it in a wrong order, then this would happen:

DROP TABLE emp;
DROP TABLE dept;

CREATE TABLE "HR"."EMP" 
("EMP_ID" NUMBER, 
 "NAME" VARCHAR2(20), 
 "MGR_ID" NUMBER, 
 "DEPT_ID" NUMBER, 
 CONSTRAINT "PK_EMP" PRIMARY KEY ("EMP_ID") ENABLE, 
 CONSTRAINT "FK_EMP_MGR_ID" FOREIGN KEY ("MGR_ID")
  REFERENCES "HR"."EMP" ("EMP_ID") ENABLE, 
 CONSTRAINT "FK_EMP_DEPT_ID" FOREIGN KEY ("DEPT_ID")
  REFERENCES "HR"."DEPT" ("DEPT_ID") ENABLE
);

Error at Command Line : 38 Column : 19
Error report -
SQL Error: ORA-00942: table or view does not exist

The referential constraint fails because there is no master table. There is a simple solution to this problem. Using the SET_TRANSFORM_PARAM procedure of the DBMS_METADATA package, we can tell Oracle that we want:

• constraints to be created separately, using the ALTER statements – by setting the CONSTRAINTS_AS_ALTER parameter to true – and
• referential constraints to be skipped during the generation of the DDL, by setting REF_CONSTRAINTS to false.

Wait.. what?! That’s your answer?! Well, hold your horses! I’m not quite done yet. You can separately extract the referential constraints and run all of them after all the tables are created. Let’s try that:

BEGIN
  DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.session_transform,
                                    'CONSTRAINTS_AS_ALTER', true);
  DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.session_transform,
                                    'REF_CONSTRAINTS', false);
END;

Now, let’s generate the DDLs:

SELECT dbms_metadata.get_ddl('TABLE', table_name)
  FROM user_tables
WHERE table_name in ('DEPT', 'EMP');

CREATE TABLE "HR"."DEPT" 
("DEPT_ID" NUMBER, 
"NAME" VARCHAR2(20)
);

ALTER TABLE "HR"."DEPT" ADD CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPT_ID") ENABLE;

CREATE TABLE "HR"."EMP"
("EMP_ID" NUMBER, 
 "NAME" VARCHAR2(20), 
 "MGR_ID" NUMBER, 
 "DEPT_ID" NUMBER
);

ALTER TABLE "HR"."EMP" ADD CONSTRAINT "PK_EMP" PRIMARY KEY ("EMP_ID") ENABLE;

As you can see, there are no referential constraints in the output now, and the remaining constraints are created through the ALTER statements.

Finally, you can get the referential constraints using the GET_DEPENDENT_DDL function with the REF_CONSTRAINT object type parameter:

SELECT dbms_metadata.get_dependent_ddl('REF_CONSTRAINT', table_name)
  FROM user_tables t
WHERE table_name IN ('EMP', 'DEPT')
  AND EXISTS (SELECT 1
                FROM user_constraints
              WHERE table_name = t.table_name
                AND constraint_type = 'R');

As a result, we get:

ALTER TABLE "HR"."EMP" ADD CONSTRAINT "FK_EMP_DEPT_ID" FOREIGN KEY ("DEPT_ID")
  REFERENCES "HR"."DEPT" ("DEPT_ID") ENABLE;

ALTER TABLE "HR"."EMP" ADD CONSTRAINT "FK_EMP_MGR_ID" FOREIGN KEY ("MGR_ID")
  REFERENCES "HR"."EMP" ("EMP_ID") ENABLE;

Now you can easily extract all the necessary DDLs for your tables without worrying about the order of creation, and, later on, add all referential constraints.

You may ask, why am I checking in the SELECT above if the given table has any referential constraints? Well, the GET_DEPENDENT_DDL function will raise an error if given object, the DEPT table, for example, has no dependent objects of given type (referential constraints in this case). Take a look:

SELECT dbms_metadata.get_dependent_ddl('REF_CONSTRAINT', 'DEPT')
  FROM dual;

31608. 00000 - specified object of type REF_CONSTRAINT not found
*Cause:    The specified object was not found in the database.
*Action:   Correct the object specification and try the call again.

Further Reading & Useful Links

There is a lot more to the DBMS_METADATA package. Below is the link to Oracle’s documentation about it:

• DBMS_METADATA – Oracle Doc

---

I hope you enjoyed my article. If you have found any errors in it (even typos), you think that I haven’t explained anything clearly enough or you have an idea how I could make the article better – please, do not hesitate to contact me, or leave a comment.