Generate Enable Constraint Statements (enable_constraint.gen)

What This Script Does

This generator script creates ALTER TABLE statements to enable disabled constraints. It orders primary/unique/check constraints first, then foreign keys to handle dependencies properly.

The Script

rem  enable_constraint.gen
rem
select 'alter table ' || owner || '.' || table_name ||
       ' enable 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 = 'DISABLED'
 order by decode(constraint_type,'R',2,1), owner, table_name, constraint_name
/

Usage

-- Basic usage
@enable_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 enable constraint EMP_EMP_ID_PK;
alter table HR.EMPLOYEES enable constraint EMP_EMAIL_UK;
alter table HR.EMPLOYEES enable constraint EMP_SALARY_MIN;
alter table HR.EMPLOYEES enable constraint EMP_DEPT_FK;

Key Features

Smart Ordering: Enables primary/unique constraints before foreign keys
Constraint Types: Handles Primary (P), Unique (U), Referential (R), and Check (C) constraints
Status Aware: Only generates statements for currently disabled constraints
Dependency Management: Proper ordering minimizes enable failures

Common Use Cases

Enable All Constraints After Data Load

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

Enable All Disabled Constraints in Schema

@enable_constraint.gen
-- Enter: SCOTT for owner, % for table_name

Disable Constraint Generator - Disable constraints
Compile Invalid Generator - Compile invalid objects after enabling constraints