Drop table does not work even after disabling constraints
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Drop table does not work even after disabling constraints

  1. #1
    Join Date
    Jan 2004
    Location
    Bangalore, India
    Posts
    66

    Drop table does not work even after disabling constraints

    Hi,

    I have a table whose Primary Key column is used to define the Foreign Key of seven other tables.
    I now want to drop the parent table: Disabled the seven FKs and tried, didnt work; it gave ora-02449 error.
    The following query was used to determine FKs:
    select parent.table_name "P", child.table_name "C"
    from user_constraints parent, user_constraints child
    where child.constraint_type='R'
    and parent.constraint_name=child.r_constraint_name
    and parent.owner = child.owner
    and parent.table_name = 'TABLE_NAME'
    order by parent.table_name, child.table_name;

    I also imported the datapump file into a sql file and verified; there are only 7 FK constraints on the above parent table.

    Also, it is to be mentioned that some tables have a composite Primary Key defined with the column name same as the above table's primary key column name.
    Would this have anything to do with the issue at hand?
    Please note that the above composite PK tables do not have any FK on the offending table.

    Please help!

    Regards,
    Suhas

  2. #2
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    Disabling FKs does not help. You have to drop them.

    $ oerr ora 02449
    02449, 00000, "unique/primary keys in table referenced by foreign keys"
    // *Cause: An attempt was made to drop a table with unique or
    // primary keys referenced by foreign keys in another table.
    // *Action: Before performing the above operations the table, DROP the
    // foreign key constraints in other tables.
    You can see what
    // constraints are referencing a table by issuing the following
    // command:
    // SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = "tabnam";


    And the manual says:

    If the table to be dropped contains any primary or unique keys referenced by foreign keys of other tables and you intend to drop the FOREIGN KEY constraints of the child tables, then include the CASCADE clause in the DROP TABLE statement, as shown below:

    DROP TABLE hr.admin_emp CASCADE CONSTRAINTS;
    Ales
    The whole difference between a little boy and an adult man is the price of toys

  3. #3
    Join Date
    Jan 2004
    Location
    Bangalore, India
    Posts
    66
    Yes Ales,

    You are right. After I got nowhere with disabling constraints, I just dropped them and it worked fine.
    I would rather have the disable work than drop.

    Actually, I was doing a kind of reorg, making an existing table to a partitioned table using the Data Pump (export/import) method: FYI

    Thanks for the kind reply

    Regards,
    Suhas

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by suhasd75 View Post
    I just dropped them and it worked fine.
    Please do tell you have the DDL needed to recreate these FKs.

    If the whole process is done during a maintenance window you may want to recreate FKs with NOVALIDATE option -just to do it faster.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  5. #5
    Join Date
    Jan 2004
    Location
    Bangalore, India
    Posts
    66
    Quote Originally Posted by PAVB View Post
    Please do tell you have the DDL needed to recreate these FKs.

    If the whole process is done during a maintenance window you may want to recreate FKs with NOVALIDATE option -just to do it faster.
    Absolutely, PAVB.

    I ensured that I had all relevant scripts and also a logical backup (in production, I would do both physical and logical backup).

    And thanks for the NOVALIDATE tip; I would have done the same during the actual process.

    Thanks again everyone, for the quick reply with resolutions and tips!

    Suhas

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