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;
Ales The whole difference between a little boy and an adult man is the price of toys
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.