Query, disable, enable, and delete Foreign keys in Oracle
1. query the Foreign keys of all tables:
select table_name, constraint_name from user_constraints where constraint_type = 'R';
2. Disable all foreign key constraints and use the following SQL statement to generate the corresponding SQL script:
select 'alter table ' || table_name || ' disable constraint ' || constraint_name || ';'from user_constraintswhere constraint_type = 'R';
The generated SQL statement is similar to the following statement:
alter table HRMIS_RELIC_DAMAGE disable constraint FK_HRMIS_RE_0927;alter table HRMIS_RELIC_BASE disable constraint FK_HRMIS_RE_0922;
3. Enable all foreign key constraints and use the following SQL statement to generate the corresponding SQL script:
select 'alter table ' || table_name || ' enable constraint ' || constraint_name || ';'from user_constraintswhere constraint_type = 'R';
The generated SQL statement is similar to the following statement:
alter table HRMIS_RELIC_DAMAGE enable constraint FK_HRMIS_RE_0927;alter table HRMIS_RELIC_BASE enable constraint FK_HRMIS_RE_0922;
4. Delete all foreign key constraints and use the following SQL statement to generate the corresponding SQL script:
select 'alter table ' || table_name || ' drop constraint ' || constraint_name || ';'from user_constraintswhere constraint_type = 'R';
The generated SQL statement is similar to the following statement:
alter table HRMIS_RELIC_DAMAGE drop constraint FK_HRMIS_RE_0927;alter table HRMIS_RELIC_BASE drop constraint FK_HRMIS_RE_0922;