DROP TABLE … CASCADE CONSTRAINTS
create table tq84_A (
id number primary key
);
create table tq84_B (
id number primary key,
id_a references TQ84_A
);
alter table tq84_A add id_b references tq84_B;
--
-- The following statements both cause ORA-02449: unique/primary keys in table referenced by foreign keys
--
drop table tq84_A;
drop table tq84_B;
--
-- Tables can be dropped with DROP TABLE … CASCADE CONSTRAINTS:
--
drop table tq84_A cascade constraints;
drop table tq84_B cascade constraints;
Remove foreign keys (Brute force)
The following script is quite a brute force attempt to remove all foreign keys which reference a table so that the table can be dropped.
If this script should be used, of course, is in the eye of the beholder.
create table tq84_A (
id number primary key
);
create table tq84_B (
id number primary key,
id_a references TQ84_A
);
alter table tq84_A add id_b references tq84_B;
drop table tq84_A;
-- ORA-02449: unique/primary keys in table referenced by foreign keys
--
-- Brute force!
-- Remove all foreign key constraints that
-- refer to table tq84_A.
--
declare
tab_name varchar2(30) := 'TQ84_A';
begin
for r in (
select
fk.constraint_name fk_cons_name,
fk.table_name fk_tab_name
from
user_constraints pk join
user_constraints fk on pk.constraint_name = fk.r_constraint_name
where
pk.table_name = tab_name and
pk.constraint_type = 'P'
) loop
-- dbms_output.put_line(r.fk_cons_name || ': ' || r.fk_tab_name);
execute immediate 'alter table ' || r.fk_tab_name || ' drop constraint ' || r.fk_cons_name;
end loop;
end;
/
drop table tq84_A;
drop table tq84_B;
Compare the effect of this script with alter table disable primary key cascade.