今天遇到一个问题，在删除表的时候报的错ORA-02292: 违反完整约束条件 (用户名.约束名) - 已找到子记录。
原因是:删除该表时，有依赖该表的子表数据，需要删除该条记录或者禁用约束。如果记录多，查找很不方便。所以最好就是禁用约束。那么表那么多我们怎么看约束在哪个表里呢。这里需要查询 constraint_name 表。让我们看看 desc constraint_name; (红色部分)
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 should 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
可以看到表名和约束名。我们用语句来禁用该约束
alter table 表名 disable constraint 约束名;
........
当然删除了表以后，别忘记在启用该约束
alter table 表名 enable constraint 约束名;
这里就全都完成了
