Enable and disable the Primary key constraint for the table in Oracle
For enable the primary key constraint for the table:
ALTER TABLE schema_name.table_name DISABLE PRIMARY KEY;
For disable the primary key constraint for the table:
ALTER TABLE schema_name.table_name ENABLE PRIMARY KEY;
For Enable and Disable the Primary key for all tables in Schema:
For enable primary key constraint for all tables in schema:
DECLARE
v_owner VARCHAR2(30) := UPPER('&1');
BEGIN
FOR c in (select table_name, owner from all_constraints where contraint_type='P' and owner = v_owner )
LOOP
EXECUTE IMMEDIATE 'ALTER TABLE '||c.owner||'.'||c.table_name||' ENABLE PRIMARY KEY'
END LOOP;
END;
For disable primary key constraint for all tables in Schema:
DECLARE
v_owner VARCHAR2(30) := UPPER('&1');
BEGIN
FOR c in (select table_name, owner from all_constraints where contraint_type='P' and owner = v_owner )
LOOP
EXECUTE IMMEDIATE 'ALTER TABLE '||c.owner||'.'||c.table_name||' DISABLE PRIMARY KEY'
END LOOP;
END;