problem with "drop table"

[raf]

Hi,
I've table BP:

CREATE TABLE BP (
BV_ID VARCHAR2 (32) DEFAULT NULL NOT NULL,
BP_ID VARCHAR2 (32) DEFAULT NULL NOT NULL,
CONSTRAINT BP_PK
PRIMARY KEY ( BV_ID, BP_ID )
)

BP has more 100 records:

Now I'd like to drop table BP:

drop table BP;
ORA-02449: unique/primary keys in table referenced by foreign keys

ALTER TABLE BP DISABLE CONSTRAINT BP_PK;
ORA-02297: cannot disable constraint (BP_PK) - dependencies exist

I tried disable all constraints with:

select 'alter table '||table_name||' disable constraints '||constraint_name||';'
from user_cons_columns
where column_name='BV_ID'
or column_name='BP_ID'

but when I try drop table BP;
ORA-02449: unique/primary keys in table referenced by foreign keys

also truncate table BP:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys

How can I drop table BP and disable all referenced by enabled foreign keys on table BP?

Thanks!

[tamilselvan]

SQL> CREATE TABLE BP (
BV_ID VARCHAR2 (32) DEFAULT NULL NOT NULL,
BP_ID VARCHAR2 (32) DEFAULT NULL NOT NULL,
CONSTRAINT BP_PK
PRIMARY KEY ( BV_ID, BP_ID )
) ;

Table created.

SQL> insert into bp select substr(object_name,1,32), substr(object_name,1,32) from dba_objects
2 where rownum <101 ;

100 rows created.

SQL> commit ;

Commit complete.

SQL> drop table bp ;

Table dropped.


I don't see any problem in dropping a table.
However, if you have referential integrity constraint, then you need to disable it also.

Tamil

[graham_o]

to drop a table that is referenced by foreign keys I believe you need to drop those foreign key constraints, not just disable them.

[davey23uk]

i concur

[WilliamR]

Figuring out all the referencing constraints sounds like a lot of work when you can just use the CASCADE CONSTRAINTS (http://download-uk.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_94a.htm) clause.