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;

Script to Enable and Disable the Primary key for all tables in Schema in Oracle:

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;

		

Related

Leave a Reply Cancel reply