Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2002
    Posts
    227

    Unanswered: I cannot "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 2005
    Posts
    14
    Hi
    Try listing the constraints that reference your primary key using

    select * from dba_constraints where r_constraint_name = 'BP_PK';

    These are the constraints you will have to drop before you can drop BP

  3. #3
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    I'm surprised that any constraints exist referencing BP. Are there some other steps missing where tables and foreign keys are created?

    You can't disable referencing constraints when you drop the table, you have to drop them. The easiest way is to use the CASCADE CONSTRAINTS clause of DROP TABLE.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •