problem with "drop table"
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: problem with "drop table"

  1. #1
    Join Date
    Jul 2002
    Posts
    228

    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!

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    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

  3. #3
    Join Date
    Apr 2006
    Posts
    50
    to drop a table that is referenced by foreign keys I believe you need to drop those foreign key constraints, not just disable them.

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    i concur

  5. #5
    Join Date
    Jun 2005
    Location
    London, UK
    Posts
    159
    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
  •  


Click Here to Expand Forum to Full Width