The IN_DB_UTIL_SP_DROP_FK stored procedure is used to drop foreign key constraints on the specified table. This procedure is intended to be used by the IN_DB_UPGRADE package for schema upgrade actions.
You must provide the table name and either the foreign key name to be dropped OR indicate the SCOPE of constraints to be dropped for the table. [ALL|FROM|TO]
| Parameter | Description | |
|---|---|---|
| TABLE_NAME | Type: | INPUT |
| Datatype: | SYSNAME | |
| Default Value: | NULL | |
| Description | The name of the table for which to drop the foreign keys from. The FK_NAME or the SCOPE must also be provided. | |
| FK_NAME | Type: | INPUT |
| Datatype: | SYSNAME | |
| Default Value: | NULL | |
| Description | The name of the foreign key to drop. If no foreign key name is specified then the scope must be specified. | |
| SCOPE | Type: | INPUT |
| Datatype: | NVARCHAR(4) | |
| Default Value: | NULL | |
| Description | The scope of the foreign keys, in relation to the specified table. | |
| Option |
TO = Drops all foreign keys that reference the specified table. FROM = Drops all foreign keys on the specified table that reference other tables. ALL = Drops all foreign keys TO and FROM the specified table. |
|
| DEBUG | Type: | INPUT |
| Datatype: | NVARCHAR(3) | |
| Default Value: | NO | |
| Description | Specifies whether to display additional debugging information during execution, including background operations such as session setup and state changes and call stack details. | |
| Options | YES = Display additional details to the Messages tab NO = Do not display additional details to the Message tab |
|
| V_RETURN_VAL | Type: | OUTPUT |
| Datatype: | INTEGER | |
| Default Value: | 0 | |
| Description | Specifies whether the execution of the procedure was successful or
not. 0 = Success 1 = Error |
|
Examples
DECLARE @V_RETURN_VAL INT EXEC @V_RETURN_VAL = IN_DROP_FK 'IN_AUDIT', NULL, 'TO' PRINT @V_RETURN_VAL -- Drop the FK_AD_A_ID foreign key from the IN_AUDIT_DETAIL table EXEC IN_DROP_FK 'IN_AUDIT_DETAIL', 'FK_AD_A_ID' -- Drop all foreign keys that reference the IN_AUDIT table EXEC IN_DROP_FK 'IN_AUDIT', NULL, 'TO'