愛華網(wǎng)db2 在修改表名的時(shí)候，表上不能有外鍵約束，不能被視圖引用。。。。。。。。。
--例子
RENAME TABLETPA_AVMACCOUNTTOTCASH_AVMACCOUNT;
RENAME TABLE TPA_BOMACCOUNT TOTCASH_BOMACCOUNT;
RENAME TABLE TPA_CSCTICKETTOTCASH_CSCTICKET;
RENAME TABLE TPA_HEADINFO TOTCASH_HEADINFO;
--db2 報(bào)了一大堆錯(cuò)誤（剛開始有點(diǎn)怕怕的）
在發(fā)出 RENAME 語句之前，刪除依賴于此表的視圖、具體化查詢表、觸發(fā)器、SQL
函數(shù)、SQL 方法、檢查約束、引用約束或 XSR 對象。對于依賴于此表的視圖或具
體化查詢表，請查詢 SYSCAT.VIEWDEP 并使表與 BSCHEMA 和 BNAME 列匹配。對
于依賴于此表的觸發(fā)器，請查詢 SYSCAT.TRIGDEP 并使表與 BSCHEMA 和 BNAME
列匹配。對于 SQL 函數(shù)或 SQL 方法，請查詢 SYSCAT.FUNCDEP 并使表與 BSCHEMA
和 BNAME 列匹配。對于表的檢查約束，請查詢 SYSCAT.CHECKS 并使表與
TABSCHEMA 和 TABBNAME 列匹配。對于依賴于此表的引用約束，請查詢
SYSCAT.REFERENCES 并使表與 TABSCHEMA 和 TABNAME 列或者 REFTABSCHEMA 和
REFTABNAME 列匹配。對于為了進(jìn)行將此表作為目標(biāo)的分解而啟用的 XSR 對象，
請查詢 SYSCAT.XSROBJECTDEP 并使表與 BSCHEMA 和 BNAME列匹配。
--看來限制條件真多，根據(jù)錯(cuò)誤提示，查看對象的依賴性
select * from SYSCAT.VIEWDEP where bschema='L_SZ_V16' AND BNAMEIN('TPA_AVMACCOUNT','TPA_BOMACCOUNT','TPA_CSCTICKET','TPA_HEADINFO');
select * from SYSCAT.TRIGDEP wherebschema='L_SZ_V16' AND BNAMEIN('TPA_AVMACCOUNT','TPA_BOMACCOUNT','TPA_CSCTICKET','TPA_HEADINFO');
select * from SYSCAT.FUNCDEPwhere bschema='L_SZ_V16' AND BNAMEIN('TPA_AVMACCOUNT','TPA_BOMACCOUNT','TPA_CSCTICKET','TPA_HEADINFO');
SELECT * FROM SYSCAT.CHECKSWHERE TABSCHEMA='L_SZ_V16' AND TABNAMEIN('TPA_AVMACCOUNT','TPA_BOMACCOUNT','TPA_CSCTICKET','TPA_HEADINFO');
SELECT * FROM SYSCAT.REFERENCES WHERETABSCHEMA='L_SZ_V16' AND TABNAMEIN('TPA_AVMACCOUNT','TPA_BOMACCOUNT','TPA_CSCTICKET','TPA_HEADINFO');
SELECT * FROM SYSCAT.REFERENCES WHEREREFTABSCHEMA='L_SZ_V16' AND REFTABNAMEIN('TPA_AVMACCOUNT','TPA_BOMACCOUNT','TPA_CSCTICKET','TPA_HEADINFO');
SELECT * FROM SYSCAT.XSROBJECTDEP WHEREbschema='L_SZ_V16' AND BNAMEIN('TPA_AVMACCOUNT','TPA_BOMACCOUNT','TPA_CSCTICKET','TPA_HEADINFO');
查看后，發(fā)現(xiàn)表上有外鍵約束，其他的都沒有
--執(zhí)行刪除外鍵的sql
ALTER TABLE TPA_AVMACCOUNT DROP FOREIGN KEYFK57641F8FB2FBC862;ALTERTABLE TPA_CSCTICKET DROP FOREIGN KEY FK3505A84597A0F542;
COMMIT;
RENAME TABLE TPA_AVMACCOUNT TOTCASH_AVMACCOUNT;RENAMETABLE TPA_BOMACCOUNT TOTCASH_BOMACCOUNT;RENAMETABLE TPA_CSCTICKET TOTCASH_CSCTICKET;RENAMETABLE TPA_HEADINFO TOTCASH_HEADINFO;
COMMIT;
--執(zhí)行創(chuàng)建外鍵的sql，表名TPA_開頭的都修改為 TCASH開頭，然后執(zhí)行。
ALTER TABLE TCASH_CSCTICKET ADD CONSTRAINT FK3505A84597A0F542FOREIGN KEY (BOMACCOUNT_RECORDID) REFERENCESTCASH_BOMACCOUNT(RECORDID) ON DELETE CASCADE ON UPDATE RESTRICTENFORCED ENABLE QUERYOPTIMIZATION;ALTERTABLE TCASH_AVMACCOUNT ADD CONSTRAINT FK57641F8FB2FBC862 FOREIGNKEY (HEADINFO_RECORDID) REFERENCES TCASH_HEADINFO(RECORDID) ONDELETE CASCADE ON UPDATE RESTRICT ENFORCED ENABLEQUERYOPTIMIZATION;COMMIT;
