The GET_FK_DDL stored procedure is used to generate the DDL for a specific foreign key constraint that references the specified table name.
| Parameter | Description | |
|---|---|---|
| SCHEMA_NAME | Type: | INPUT |
| Datatype: | VARCHAR2(128) | |
| Default Value: | INUSER | |
| Description | The schema name that owns the table that is referenced by the foreign key. | |
| FK_NAME | Type: | INPUT |
| Datatype: | VARCHAR2(128) | |
| Default Value: | None | |
| Description | The name of the FK that references the PK or Unique index on the specified table. | |
| FK_DDL | Type: | RETURN |
| Datatype: | VARCHAR2(128) | |
| Default Value: | None | |
| Description | The generated DDL for the FK(s) that reference the specified table | |
Exceptions
Example
SET SERVEROUTPUT ON;
DECLARE
V_FK_DDL VARCHAR2(2000);
BEGIN
FOR fk IN
(
SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'IN_DOC' AND CONSTRAINT_TYPE = 'R'
ORDER BY CONSTRAINT_NAME
)
LOOP
IN_DB_UTIL.GET_FK_DDL('INUSER', fk.CONSTRAINT_NAME, V_FK_DDL);
IN_DB_UTIL.PRINT_SQL(V_FK_DDL);
END LOOP;
END;
/
Example Output
ALTER TABLE "INUSER"."IN_DOC" ADD CONSTRAINT "FK_D_DOC_TYPE_ID" FOREIGN KEY ("DOC_TYPE_ID")
REFERENCES "INUSER"."IN_DOC_TYPE" ("DOC_TYPE_ID") ENABLE;
ALTER TABLE "INUSER"."IN_DOC" ADD CONSTRAINT "FK_DOC_DRAWER_ID" FOREIGN KEY ("DRAWER_ID")
REFERENCES "INUSER"."IN_DRAWER" ("DRAWER_ID") ENABLE;
ALTER TABLE "INUSER"."IN_DOC" ADD CONSTRAINT "FK_DOC_INSTANCE_ID" FOREIGN KEY ("INSTANCE_ID")
REFERENCES "INUSER"."IN_INSTANCE" ("INSTANCE_ID") ENABLE;
PL/SQL procedure successfully completed.