The DROP_PK stored procedure is used to drop the primary key from a single table.
If the primary key index is currently being referenced by any foreign key constraints, preventing the constraint from being dropped, then the PK_REFERENCED exception will be raised and the foreign keys that reference the primary key constraint will be listed.
| Parameter | Description | |
|---|---|---|
| SCHEMA_NAME | Type: | INPUT |
| Datatype: | VARCHAR2(128) | |
| Default Value: | INUSER | |
| Description | The schema name that owns the table with the primary key being dropped. | |
| TABLE_NAME | Type: | INPUT |
| Datatype: | VARCHAR2(128) | |
| Default Value: | None | |
| Description | The name of the table with the primary key being dropped. | |
Exceptions
- FAILED_CHECK
- NO_DATA_FOUND – The specified table does not have a primary key.
- PK_REFERENCED – The primary key is currently referenced by foreign keys.
- OTHERS
Example
EXEC IN_DB_UTIL.DROP_PK('INUSER', 'IN_AUDIT');
Example output
EXEC IN_DB_UTIL.DROP_PK('INUSER', 'IN_AUDIT');
ALTER TABLE INUSER.IN_AUDIT DROP PRIMARY KEY DROP INDEX
The specified primary key constraint is referenced by the following foreign keys:
Table: INUSER.IN_AUDIT_DETAIL - FK constraint: FK_AD_A_ID
Table: INUSER.IN_AUDIT_OBJ - FK constraint: FK_AO_A_ID
PL/SQL procedure successfully completed.