-
problem with "drop table"
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!
-
Code:
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
-
to drop a table that is referenced by foreign keys I believe you need to drop those foreign key constraints, not just disable them.
-
-
Figuring out all the referencing constraints sounds like a lot of work when you can just use the CASCADE CONSTRAINTS clause.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|