The DISABLE_FK stored procedure is used to disable a single foreign key or all the foreign keys to and/or from a single table.
The SCOPE parameter is used to specify the scope for which to disable the foreign keys as related to the specified table.
- FROM includes the foreign keys on the specified table that reference any other table.
- TO includes the foreign keys from any other table that reference the specified table.
- ALL includes all foreign keys to and from the specified table.
| Parameter | Description | |
|---|---|---|
| SCHEMA_NAME | Type: | INPUT |
| Datatype: | VARCHAR2(128) | |
| Default Value: | INUSER | |
| Description | The schema name that owns the table with the foreign key(s) being disabled. | |
| TABLE_NAME | Type: | INPUT |
| Datatype: | VARCHAR2(128) | |
| Default Value: | None | |
| Description | The name of the table with the foreign key(s) being disabled. | |
| SCOPE | Type: | INPUT |
| Datatype: | VARCHAR2(4) | |
| Default Value: | ALL | |
| Description | The scope for which to disable the foreign keys as related to the specified table. | |
| Options |
FROM TO ALL |
|
| FK_NAME | Type: | INPUT |
| Datatype: | VARCHAR2(128) | |
| Default Value: | NULL | |
| Description | The name of the foreign key being dropped if only dropping one foreign key. | |
| VERBOSE | Type: | INUSER |
| Datatype: | VARCHAR2(3) | |
| Default Value: | Yes | |
| Description | s used to indicate if you want to display the status of the foreign keys before and after disabling. | |
Exceptions
Examples
SET SERVEROUTPUT ON;
EXEC IN_DB_UTIL.DISABLE_FK('INUSER', 'IN_DOC', [ALL|TO|FROM], [FK_NAME], [YES|NO]);
EXEC IN_DB_UTIL.DISABLE_FK('INUSER', 'IN_DOC', 'TO', NULL, 'YES');
EXEC IN_DB_UTIL.DISABLE_FK('INUSER', 'IN_DOC', 'FROM', NULL, 'YES');
EXEC IN_DB_UTIL.DISABLE_FK('INUSER', 'IN_DOC', 'ALL', NULL, 'NO');
EXEC IN_DB_UTIL.DISABLE_FK('INUSER', 'IN_DOC', NULL, 'FK_D_DOC_TYPE_ID', 'NO');
Example Output
SQL> EXEC IN_DB_UTIL.DISABLE_FK('INUSER', 'IN_AUDIT', 'ALL', NULL, 'YES');
Referential Integrity Constraints for the INUSER.IN_AUDIT table:
INUSER.IN_AUDIT References: INUSER.IN_AUDIT Referenced By:
Table INUSER.IN_AUDIT_DETAIL Foreign Key FK_AD_A_ID References PK_AUDIT is ENABLED and VALIDATED
Table INUSER.IN_AUDIT_OBJ Foreign Key FK_AO_A_ID References PK_AUDIT is ENABLED and VALIDATED
-------------------------------------------------------------
Disable all qualifying foreign key constraints
-------------------------------------------------------------
ALTER TABLE INUSER.IN_AUDIT_DETAIL DISABLE CONSTRAINT FK_AD_A_ID
ALTER TABLE INUSER.IN_AUDIT_OBJ DISABLE CONSTRAINT FK_AO_A_ID
-------------------------------------------------------------
Referential Integrity Constraints for the INUSER.IN_AUDIT table:
INUSER.IN_AUDIT References:
INUSER.IN_AUDIT Referenced By:
Table INUSER.IN_AUDIT_DETAIL Foreign Key FK_AD_A_ID References PK_AUDIT is DISABLED and NOT VALIDATED
Table INUSER.IN_AUDIT_OBJ Foreign Key FK_AO_A_ID References PK_AUDIT is DISABLED and NOT VALIDATED
PL/SQL procedure successfully completed.