Enable and disable the Primary Key Constraint for table in Oracle

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;
This entry was posted in Oracle on by .
Unknown's avatar

About SandeepSingh

Hi, I am working in IT industry with having more than 15 year of experience, worked as an Oracle DBA with a Company and handling different databases like Oracle, SQL Server , DB2 etc Worked as a Development and Database Administrator.

Leave a Reply