The ENABLE_FK stored procedure is used to enable and validate 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 enable and validate 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 enabled and validated. | |
| TABLE_NAME | Type: | INPUT |
| Datatype: | VARCHAR2(128) | |
| Default Value: | None | |
| Description | The name of the table with the foreign key(s) being enabled validated. | |
| SCOPE | Type: | INPUT |
| Datatype: | VARCHAR2(4) | |
| Default Value: | ALL | |
| Description | The scope for which to enable and validate 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 enabled and validated if only enabling one foreign key. | |
| VERBOSE | Type: | INUSER |
| Datatype: | VARCHAR2(3) | |
| Default Value: | Yes | |
| Description | Is used to indicate if you want to display the status of the foreign keys before and after being enabled and validated. | |
| Options |
Yes No |
|
Exceptions
Examples
SET SERVEROUTPUT ON;
EXEC IN_DB_UTIL.ENABLE_FK('INUSER', 'IN_DOC', [ALL|TO|FROM], [FK_NAME], [YES|NO]);
EXEC IN_DB_UTIL.ENABLE_FK('INUSER', 'IN_DOC', 'TO', NULL, 'YES');
EXEC IN_DB_UTIL.ENABLE_FK('INUSER', 'IN_DOC', 'FROM', NULL, 'YES');
EXEC IN_DB_UTIL.ENABLE_FK('INUSER', 'IN_DOC', 'ALL', NULL, 'NO');
EXEC IN_DB_UTIL.ENABLE_FK('INUSER', 'IN_DOC', NULL, 'FK_D_DOC_TYPE_ID', 'NO');
Example Output
SQL> EXEC IN_DB_UTIL.ENABLE_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 DISABLED and NOT VALIDATED
Table INUSER.IN_AUDIT_OBJ Foreign Key FK_AO_A_ID References PK_AUDIT is ENABLED and NOT VALIDATED
-------------------------------------------------------------
Enable and validate all qualifying foreign key constraints
-------------------------------------------------------------
ALTER TABLE INUSER.IN_AUDIT_DETAIL ENABLE CONSTRAINT FK_AD_A_ID
ALTER TABLE INUSER.IN_AUDIT_DETAIL MODIFY CONSTRAINT FK_AD_A_ID VALIDATE
ALTER TABLE INUSER.IN_AUDIT_OBJ ENABLE CONSTRAINT FK_AO_A_ID
ALTER TABLE INUSER.IN_AUDIT_OBJ MODIFY CONSTRAINT FK_AO_A_ID VALIDATE
-------------------------------------------------------------
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
PL/SQL procedure successfully completed.