The DROP_FK stored procedure is used to drop a single foreign key or all the foreign keys to and/or from a specified table.
The SCOPE parameter is used to specify the scope for which to drop 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 dropped. | |
| TABLE_NAME | Type: | INPUT |
| Datatype: | VARCHAR2(128) | |
| Default Value: | None | |
| Description | The name of the table with the foreign key(s) being dropped. | |
| SCOPE | Type: | INPUT |
| Datatype: | VARCHAR2(4) | |
| Default Value: | From | |
| Description | The scope for which to drop 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. | |
Exceptions
- FAILED_CHECK
- INVALID_FK – The specified foreign key does not belong to the specified table.
- OTHERS
Examples
EXEC IN_DB_UTIL.DROP_FK('INUSER', TABLE_NAME, [ALL|TO|FROM], [FK_NAME]);
SET SERVEROUTPUT ON;
EXEC IN_DB_UTIL.DROP_FK('INUSER', 'IN_AUDIT', 'ALL');
Example Output
SQL> EXEC IN_DB_UTIL.DROP_FK('INUSER', 'IN_AUDIT', 'ALL');
ALTER TABLE INUSER.IN_AUDIT_DETAIL DROP CONSTRAINT FK_AD_A_ID
ALTER TABLE INUSER.IN_AUDIT_OBJ DROP CONSTRAINT FK_AO_A_ID
PL/SQL procedure successfully completed.