Drop table does not work even after disabling constraints
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
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.
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
// 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;
The whole difference between a little boy and an adult man is the price of toys
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
Please do tell you have the DDL needed to recreate these FKs.
Originally Posted by suhasd75
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.
Originally Posted by 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!
Click Here to Expand Forum to Full Width