Today I encountered a problem with the error ORA-02292 reported when deleting the table: violation of the complete constraints (username. constraint name)-subrecord found.
The reason is: When you delete a table that has sub-table data dependent on the table, you need to delete the record or disable the constraint. If there are many records, it is inconvenient to search. So it is best to disable the constraint. So what do we know about the table with so many constraints. Query the constraint_name table. Let's take a look at DESC constraint_name; (red part)
Name type nullable default comments
-----------------------------------------------------------------------------------------------------------------------
Owner varchar2 (30) Owner of the table
Constraint_name varchar2 (30) name associated with constraint Definition
Constraint_type varchar2 (1) Y type of constraint Definition
Table_name varchar2 (30) name associated with table with constraint Definition
Search_condition long y text of Search Condition for table check
R_owner varchar2 (30) y owner of table used in referential Constraint
R_constraint_name varchar2 (30) y name of unique constraint definition for referenced table
Delete_rule varchar2 (9) y the delete rule for a referential Constraint
Status varchar2 (8) y enforcement status of constraint-enabled or disabled
Deferrable varchar2 (14) Y is the constraint deferrable-deferrable or not deferrable
Deferred varchar2 (9) Y is the constraint deferred by default-deferred or immediate
Validated varchar2 (13) y was this constraint system validated? -Validated or not validated
Generated varchar2 (14) y was the constraint name system generated? -Generated name or user name
Bad varchar2 (3) y creating this constraint shocould give ORA-02436. Rewrite it before 2000 AD.
Rely varchar2 (4) y if set, this flag will be used in Optimizer
Last_change date y the date when this column was last enabled or disabled
Index_owner varchar2 (30) y the owner of the index used by the Constraint
Index_name varchar2 (30) y the index used by the Constraint
Invalid varchar2 (7) y
View_related varchar2 (14) y
The table name and constraint name are displayed. We use statements to disable this constraint.
Alter table table name disable constraint name;
........
After deleting a table, do not forget to enable this constraint.
Alter table table name enable constraint name;
All done here