Tag: primary keys

Here are the snippets for enabling and disabling all constraints in an oracle schema. This is useful when you create a new schema and then fill it up with the data from other databases.

For whatever reason you will need this be careful to only enable primary keys at first, otherwise all foreign key validation will fail.

Also check my post about updating sequences –

--
-- disable all
--

BEGIN
  FOR c IN
  (SELECT c.owner, c.table_name, c.constraint_name
   FROM user_constraints c, user_tables t
   WHERE c.table_name = t.table_name
   AND c.status = 'ENABLED'
   ORDER BY c.constraint_type DESC)
  LOOP
    dbms_utility.exec_ddl_statement('alter table "' || c.owner || '"."' || c.table_name || '" disable constraint ' || c.constraint_name);
  END LOOP;
END;
/

--
--enable all
--
BEGIN
  FOR c IN
  (SELECT c.owner, c.table_name, c.constraint_name
   FROM user_constraints c, user_tables t
   WHERE c.table_name = t.table_name
   AND c.status = 'DISABLED'
   --AND CONSTRAINT_NAME LIKE '%_PK%'   --first you have to enable only primary keys, otherwise foreign keys will not validate
   ORDER BY c.constraint_type)
  LOOP
    dbms_utility.exec_ddl_statement('alter table "' || c.owner || '"."' || c.table_name || '" enable constraint ' || c.constraint_name);
  END LOOP;
END;
/