不允许设置db2主键问题的解决

设置db2主键问题出错的话，应该如何处理呢？下面就教您一个不允许设置db2主键问题的解决方法，希望对您学习设置db2主键方面有所帮助。

将empno设置为主键

db2 => alter table emp add primary key (empno)

DB21034E The command was processed as an SQL statement because it was not a

valid Command Line Processor command. During SQL processing it returned:

SQL0542N “EMPNO” cannot be a column of a primary key or unique key because it

can contain null values. SQLSTATE=42831

fenced  出现问题的原因是，因为empno列在创建的时候，默认支持值为空。而在DB2中，针对这种情况，是不允许创建主键的。这点也与oracle和mysql不同。

fenced  因此，我们要添加主键，需要先修改empno，添加上约束，让此列为非空：

db2 => alter table emp alter column empno set not null

将列修改了之后，尝试添加主键，再次出错：

db2 => alter table emp add primary key (empno)

DB21034E The command was processed as an SQL statement because it was not a

valid Command Line Processor command. During SQL processing it returned:

SQL0668N Operation not allowed for reason code “7” on table “DB2INST1.EMP”.

SQLSTATE=57016

解决办法：

查看错误信息代码SQL0668N，去查看具体的code“7”的含义。

db2 => ? SQL0668N

SQL0668N Operation not allowed for reason code “<reason-code>” on table

     “<table-name>”.

Explanation:

Access to table “<table-name>” is restricted. The cause is based on the

following reason codes “<reason-code>”:

1fenced

fenced  The table is in the Set Integrity Pending No Access state.

。。。。。内容省略。。。。。。。。

7fencedThe table is in the reorg pending state. This can occur after

fenced  an ALTER TABLE statement containing a REORG-recommended

fenced  operation.

User response:

1fencedExecute the SET INTEGRITY statement with the IMMEDIATE CHECKED

fenced  option on table “<table-name>” to bring the table out of the

。。。。。内容省略。。。。。。。。

7fencedReorganize the table using the REORG TABLE command.

fenced  For a table in the reorg pending state, note that the following

fenced  clauses are not allowed when reorganizing the table:

fenced  

fenced  * The INPLACE REORG TABLE clause

fenced  * The ON DATA PARTITION clause for a partitioned table when

fenced     table has nonpartitioned indexes defined on the table

通过查看上述描述中的红色部分的文字，emp表的empno列是添加了not null约束而导致表出于了“reorg pending state”这种状态。我们可以通过以下的方法来验证：

db2 => load query table emp

Tablestate:

Reorg Pending

解决办法，参照6.1中的“User response”描述，使用REORG TABLE命令：

db2 => reorg table emp

DB20000I The REORG command completed successfully.

修改完成后，再次添加主键：

db2 => alter table emp add primary key(empno)

DB20000I The SQL command completed successfully.

查看下表的状态，若为normal则能够正常得进行操作：

db2 => load query table emp

Tablestate:

Normal

总结：对表添加主键时出现的错误，是由于之前对表的列进行了修改，添加了非空约束，导致整个表出于“Reorg Pending”状态。

针对表出现这种状况时，使用reorg table命令，将表进行reorganize即可。
 

【编辑推荐】

DB2临时表定义的方法

linux下安装DB2的步骤

DB2性能优化的十大注意事项

SQL SERVER连接DB2数据库

DB2索引创建原则