首页 > DB2 & Sql Server > SQL0668N Operation not allowed for reason code "1" on table. SQLSTATE=57016
2014
02-25

SQL0668N Operation not allowed for reason code "1" on table. SQLSTATE=57016

在db2查询一个表的时候,报如下错误:

The table is in Check Pending state. The integrity of the table is not enforced and the content of the table may be invalid.
An operation on a parent table or an underlying table that is not in a check pending state may also receive this error if a dependent table is in a check pending state.
Execute the SET INTEGRITY statement with the IMMEDIATE CHECKED option on table table-name to bring the table out of the Check Pending state.
For a user maintained materialized query table, execute the statement with the IMMEDIATE UNCHECKED option instead of the IMMEDIATE CHECKED option.:
ERROR [57016] [IBM][DB2/LINUXX8664] SQL0668N  Operation not allowed for reason code “1” on table “DM.MONTHLY_EAN_BUSINESS_PARTNR_PL_FACT”.  SQLSTATE=57016

 

可能有一行或多行违反了对数据定义的约束。此表不能用于操作。若从属表处于检查暂挂状态,则对不处于检查暂挂

状态的父表的操作也可能接收到此错误。

然后我查看了这个表的DDL脚本,发现有外键关联其他表,然后更新操作,大致脚本如下

ALTER TABLE “DM”.”table_name”
  ADD CONSTRAINT “FK_name” FOREIGN KEY
    (“column_name”)
  REFERENCES “DM”.”table_name2″
    (“column_name2”)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT
    ENFORCED
    ENABLE QUERY OPTIMIZATION;

由此段脚本可以看成,所存在的确实有约束限制。此外如果不能查看到脚本,可以使用如下sql进行检查:

set integrity for dm.table_name immediate checked;

这样就会提示有问题的约束,然后查看即可。由于我这里只是简单的测试用,我暂时删掉此约束就可以了。

最后编辑:
作者:Jerry
一个积极向上的小青年,热衷于分享--Focus on DB,BI,ETL