不允許設(shè)置db2主鍵問題的解決
設(shè)置db2主鍵問題出錯(cuò)的話，應(yīng)該如何處理呢？下面就教您一個(gè)不允許設(shè)置db2主鍵問題的解決方法，希望對(duì)您學(xué)習(xí)設(shè)置db2主鍵方面有所幫助。
將empno設(shè)置為主鍵
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 出現(xiàn)問題的原因是，因?yàn)閑mpno列在創(chuàng)建的時(shí)候，默認(rèn)支持值為空。而在DB2中，針對(duì)這種情況，是不允許創(chuàng)建主鍵的。這點(diǎn)也與oracle和mysql不同。
fenced 因此，我們要添加主鍵，需要先修改empno，添加上約束，讓此列為非空：
db2 => alter table emp alter column empno set not null
將列修改了之后，嘗試添加主鍵，再次出錯(cuò)：
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
解決辦法：
查看錯(cuò)誤信息代碼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.
。。。。。內(nèi)容省略。。。。。。。。
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
。。。。。內(nèi)容省略。。。。。。。。
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約束而導(dǎo)致表出于了“reorg pending state”這種狀態(tài)。我們可以通過以下的方法來驗(yàn)證：
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.
查看下表的狀態(tài)，若為normal則能夠正常得進(jìn)行操作：
db2 => load query table emp
Tablestate:
Normal
總結(jié)：對(duì)表添加主鍵時(shí)出現(xiàn)的錯(cuò)誤，是由于之前對(duì)表的列進(jìn)行了修改，添加了非空約束，導(dǎo)致整個(gè)表出于“Reorg Pending”狀態(tài)。
針對(duì)表出現(xiàn)這種狀況時(shí)，使用reorg table命令，將表進(jìn)行reorganize即可。
【編輯推薦】
