关闭

ORA-02292: 违反完整约束条件 (用户名.约束名) - 已找到子记录

标签: tablesystemdatedeletesearchuser
11364人阅读 评论(0) 收藏 举报
分类:

今天遇到一个问题,在删除表的时候报的错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 约束名;

这里就全都完成了                                                                                         

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:182498次
    • 积分:1819
    • 等级:
    • 排名:千里之外
    • 原创:17篇
    • 转载:20篇
    • 译文:1篇
    • 评论:25条
    文章分类
    最新评论