Many a times while migrating constraints and indexes, you might be wondering how to disable constraints and indexes in PostgreSQL as you do in Oracle. This article provides you a very good understanding on how to drop or disable constraints/indexes.
In Oracle database, constraints can be either in “ENABLE” state or “DISABLE” state. But in PostgreSQL database, constraint once created are always enabled. You do not have option to alter constraint state to “DISABLE” in PostgreSQL. Therefore in PostgreSQL database, if you have to bypass constraint checking temporarily, you have to drop constraints and later recreate them.
If a constraint is enabled, data is checked as it is entered or updated in the database, and data that does not conform to the constraint is prevented from being entered. If a constraint is disabled, then data that does not conform can be allowed to enter the database. To enforce the rules defined by integrity constraints, the constraints should always be enabled.
Why disable or drop constraints in databases?
To enforce the rules defined by integrity constraints, the constraints should always be enabled. However, consider temporarily disabling or dropping of the integrity constraints of a table for the following performance reasons:
- When loading large amounts of data into a table
- When performing batch operations that make massive changes to a table
- When importing or exporting one table at a time
In all three cases, temporarily disabling in Oracle and dropping in case of PostgreSQL database, of integrity constraints can improve the performance of the operation. It is possible to enter data that violates a constraint while that constraint is disabled. Thus, you should always enable the constraint after completing any of the operations listed in the preceding bullet list.
Disable constraints in Oracle & Drop constraints in PostgreSQL
If you have to temporarily disable constraints due to any of the reasons specified above, then the first step is to disable all foreign keys. This is because foreign keys references unique constraints or primary keys of same table or other table. Oracle does not allow to drop/disable parent objects who has enabled references from other tables.
Below code disables all foreign keys from user “SCOTT” in Oracle
begin
for i in (select constraint_name, table_name
from dba_constraints
where owner = 'SCOTT'
and constraint_type = 'R'
)
loop
execute immediate 'ALTER TABLE '||i.table_name||' DISABLE CONSTRAINT '||i.constraint_name||'';
end loop;
end;
/
Below code disables all other constraints excluding FK from user “SCOTT” in Oracle
begin
for i in (select constraint_name, table_name
from dba_constraints
where owner = 'SCOTT'
and constraint_type <> 'R'
)
loop
execute immediate 'ALTER TABLE '||i.table_name||' DISABLE CONSTRAINT '||i.constraint_name||'';
end loop;
end;
/
Dropping constraints in PostgreSQL database require you to first drop all foreign key references. This is because you can not drop a primary key or unique key constraint which is referenced through a foreign key in other dependent tables.
DROP all foreign keys from a schema “scott” in PostgreSQL
do $$
declare
rec record;
begin
for rec in select table_schema, table_name, constraint_name
from information_schema.table_constraints
where constraint_schema = lower('SCOTT')
and constraint_type = 'FOREIGN KEY'
loop
execute 'ALTER TABLE '|| rec.table_schema || '.' || rec.table_name || ' DROP CONSTRAINT ' || rec.constraint_name;
end loop;
end $$;
DROP all primary keys and unique keys from a schema “scott” in PostgreSQL
do $$
declare
rec record;
begin
for rec in select table_schema, table_name, constraint_name
from information_schema.table_constraints
where constraint_schema = lower('SCOTT')
and constraint_type IN('PRIMARY KEY', 'UNIQUE')
loop
execute 'ALTER TABLE '|| rec.table_schema || '.' || rec.table_name || ' DROP CONSTRAINT ' || rec.constraint_name;
end loop;
end $$;
DROP check constraints from a schema “scott” in PostgreSQL
do $$
declare
rec record;
begin
for rec in select conrelid::regclass::varchar table_name, conname::varchar constraint_name
from pg_constraint c
join pg_namespace n ON n.oid = c.connamespace
where contype in ('c') and conrelid::regclass::varchar <> '-'
and n.nspname = lower('SCOTT')
loop
execute 'ALTER TABLE ' || rec.table_name || ' DROP CONSTRAINT ' || rec.constraint_name;
end loop;
end $$;
Disable or Drop Indexes – Which is advisable?
If you are doing batch loads, the proper procedure is to drop and then re-create the index.
Why disable indexes?
In most releases, Oracle allows you to disable an index only if it is a function-based index. For non-function-based indexes, you disable the index by marking the index as unusable.
- DISABLE Clause : DISABLE applies only to a function-based index. This clause lets you disable the use of a function-based index. You might want to disable function-based indexes, for example, while working on the body of the function. Afterward you can either rebuild the index or specify another ALTER INDEX statement with the ENABLE keyword.
- UNUSABLE Clause : Specify UNUSABLE to mark the index or index partition(s) or index subpartition(s) UNUSABLE. An unusable index must be rebuilt, or dropped and re-created, before it can be used. While one partition is marked UNUSABLE, the other partitions of the index are still valid. You can execute statements that require the index if the statements do not access the unusable partition. You can also split or rename the unusable partition before rebuilding it.
Below code can be used to disable function-based indexes from user “SCOTT”
begin
for i in (select owner, index_name from dba_indexes where owner = 'SCOTT' and index_type = 'FUNCTION-BASED NORMAL') LOOP
execute immediate 'ALTER INDEX '||i.owner||'.'||i.index_name||' DISABLE';
end loop;
end;
/
Below code can be used to set all indexes unusable except function-based indexes from user “SCOTT”
begin
for i in (select owner, index_name from dba_indexes where owner = 'SCOTT' and index_type <> 'FUNCTION-BASED NORMAL') LOOP
execute immediate 'ALTER INDEX '||i.owner||'.'||i.index_name||' UNUSABLE';
end loop;
end;
/
Below code can be used to drop all indexes from a schema “scott” in PostgreSQL
do $$
declare
rec record;
begin
for rec in select schemaname, indexname
from pg_indexes where schemaname = lower('SCOTT')
loop
execute ' DROP INDEX ' || rec.schemaname || '.' || rec.indexname;
end loop;
end $$;