Disable all related foreign key constraints in Oracle

Check the Foreign key constraint related to tables in Oracle

Taking example of HR schema with DEPARTMENT Table. Department table has relation with Employees and Job_history tables.



SQL> SELECT table_name, constraint_name,r_constraint_name,CONSTRAINT_TYPE FROM user_constraints where r_constraint_name = 'DEPT_ID_PK';

TABLE_NAME      CONSTRAINT_NAME R_CONSTRAINT_NAME
--------------- --------------- --------------------
EMPLOYEES       EMP_DEPT_FK     DEPT_ID_PK
JOB_HISTORY     JHIST_DEPT_FK   DEPT_ID_PK

Check column involved in Foreign key constraints:

SELECT a.table_name, c.column_name
FROM user_constraints a, user_cons_columns c
WHERE a.CONSTRAINT_TYPE = 'R'
AND a.R_CONSTRAINT_NAME = 'DEPT_ID_PK'
AND c.constraint_name = a.constraint_name;

Disable all the constraints related to Department Foreign key


BEGIN
FOR I IN (SELECT table_name, constraint_name,r_constraint_name FROM user_constraint t WHERE r_constraint_name='DEPT_ID_PK') LOOP
EXECUTE IMMEDIATE ' alter table ' || I.table_name || ' disable constraint ' || i.constraint_name;
END LOOP;
END;
/

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.