Generate Disable Constraint Statements (disable_constraint.gen)

What This Script Does

This generator script creates ALTER TABLE statements to disable constraints. It orders foreign keys last to avoid dependency issues when disabling constraints.

The Script

rem  disable_constraint.gen
rem
select 'alter table ' || owner || '.' || table_name ||
       ' disable constraint ' || constraint_name || ';'
  from sys.dba_constraints
 where owner like upper('&owner')
   and table_name like upper('&table_name')
   and constraint_type in ('P','U','R','C')
   and status = 'ENABLED'
 order by decode(constraint_type,'R',2,1), owner, table_name, constraint_name
/

Usage

-- Basic usage
@disable_constraint.gen

-- When prompted, enter:
-- owner: Schema owner pattern (use % for wildcard)
-- table_name: Table name pattern (use % for wildcard)

Parameters

The script prompts for:

&owner - Schema owner pattern (use % for wildcard)
&table_name - Table name pattern (use % for wildcard)

Required Privileges

SELECT ANY DICTIONARY
-- OR --
SELECT ON DBA_CONSTRAINTS

Sample Output

alter table HR.EMPLOYEES disable constraint EMP_EMP_ID_PK;
alter table HR.EMPLOYEES disable constraint EMP_EMAIL_UK;
alter table HR.EMPLOYEES disable constraint EMP_SALARY_MIN;
alter table HR.EMPLOYEES disable constraint EMP_DEPT_FK;

Key Features

Smart Ordering: Disables referential constraints last to avoid dependency errors
Constraint Types: Handles Primary (P), Unique (U), Referential (R), and Check (C) constraints
Status Aware: Only generates statements for currently enabled constraints
Clean Syntax: Simple ALTER TABLE statements ready for execution

Common Use Cases

Disable All Constraints on Table

@disable_constraint.gen
-- Enter: HR for owner, EMPLOYEES for table_name

Disable Constraints Before Data Load

@disable_constraint.gen
-- Enter: % for owner, %_STAGING for table_name

Enable Constraint Generator - Re-enable constraints
Truncate Table Generator - Truncate tables after disabling constraints