如何解决DB2数据迁移中的外键约束问题

浅见池也

测试表
-- 创建测试主表. ID 是主键.
CREATE TABLE test_main (
  id      INT   NOT NULL,
  value   VARCHAR(10),
  PRIMARY KEY(id)  
);

-- 创建测试子表. 
CREATE TABLE test_sub (
  id      INT  NOT NULL,
  main_id INT ,
  value   VARCHAR(10),
  PRIMARY KEY(id)  
);

-- 插入测试主表数据.
INSERT INTO test_main(id, value) VALUES (1, 'ONE');
INSERT INTO test_main(id, value) VALUES (2, 'TWO');
-- 插入测试子表数据.
INSERT INTO test_sub(id, main_id, value) VALUES (1, 1, 'ONEONE');
INSERT INTO test_sub(id, main_id, value) VALUES (2, 2, 'TWOTWO');
 
 
 
默认外键约束方式
db2 => ALTER TABLE test_sub ADD CONSTRAINT main_id_cons  FOREIGN KEY (main_id)  REFERENCES  test_main;
DB20000I  SQL 命令成功完成。
db2 => DELETE
db2 (cont.) =>   test_main
db2 (cont.) => WHERE
db2 (cont.) =>   ID = 1;
DB21034E  该命令被当作 SQL 语句来处理，因为它是无效的"命令行处理器"命令。在
SQL 处理期间，它返回：
SQL0532N  因为关系 "WZQ.TEST_SUB.MAIN_ID_CONS" 限制删除，所以不能删除父行。
SQLSTATE=23504
 
测试完毕后，删除 外键约束 
ALTER TABLE test_sub DROP CONSTRAINT main_id_cons;
 
 
 
DELETE CASCADE 方式
 
db2 => ALTER TABLE test_sub
db2 (cont.) =>   ADD CONSTRAINT main_id_cons
db2 (cont.) =>     FOREIGN KEY (main_id)
db2 (cont.) =>       REFERENCES  test_main   ON DELETE CASCADE;
DB20000I  SQL 命令成功完成。
db2 => -- 测试删除主表数据. 将成功地执行.
db2 => DELETE
db2 (cont.) =>   TEST_MAIN
db2 (cont.) => WHERE
db2 (cont.) =>   ID = 1;
DB20000I  SQL 命令成功完成。
-- 测试检索子表，应该只有一条 main_id = 2 的数据.
db2 =>  SELECT * FROM test_sub;
ID          MAIN_ID     VALUE
----------- ----------- ----------
          2           2 TWOTWO
  1 条记录已选择。
 
测试完毕后，删除 外键约束 
ALTER TABLE test_sub DROP CONSTRAINT main_id_cons;
 
 
 
UPDATE CASCADE方式
DB2 不支持
db2 => ALTER TABLE test_sub
db2 (cont.) =>   ADD CONSTRAINT main_id_cons
db2 (cont.) =>     FOREIGN KEY (main_id)
db2 (cont.) =>       REFERENCES test_main
db2 (cont.) =>         ON UPDATE CASCADE;
DB21034E  该命令被当作 SQL 语句来处理，因为它是无效的"命令行处理器"命令。在
SQL 处理期间，它返回：
SQL0104N  在 "ON UPDATE" 后面找到异常标记
"CASCADE"。预期标记可能包括："RESTRICT, NO ACTION"。  SQLSTATE=42601
 
 
 
SET NULL方式
 
db2 => ALTER TABLE test_sub
db2 (cont.) =>   ADD CONSTRAINT main_id_cons
db2 (cont.) =>     FOREIGN KEY (main_id)
db2 (cont.) =>       REFERENCES  test_main
db2 (cont.) =>         ON DELETE SET NULL;
DB20000I  SQL 命令成功完成。
db2 => -- 测试删除主表数据. 将成功地执行.
db2 => DELETE TEST_MAIN WHERE ID = 2;
DB20000I  SQL 命令成功完成。
db2 =>  -- 测试检索子表，应该有一条的 main_id = null
db2 => SELECT * FROM test_sub;
ID          MAIN_ID     VALUE
----------- ----------- ----------
          2           - TWOTWO
  1 条记录已选择。
 
 

 
关于非主键的外键设置
-- 创建测试主表.  无主键.
CREATE TABLE test_main2 (
  id      INT   NOT  NULL,
  value   VARCHAR(10)
);

-- 创建测试子表. 
CREATE TABLE test_sub2 (
  id      INT   NOT  NULL,
  main_id INT,
  value   VARCHAR(10),
  PRIMARY KEY(id)  
);

-- 插入测试主表数据.
INSERT INTO test_main2(id, value) VALUES (1, 'ONE');
INSERT INTO test_main2(id, value) VALUES (2, 'TWO');
-- 插入测试子表数据.
INSERT INTO test_sub2(id, main_id, value) VALUES (1, 1, 'ONEONE');
INSERT INTO test_sub2(id, main_id, value) VALUES (2, 2, 'TWOTWO');
 
 
db2 => ALTER TABLE test_sub2
db2 (cont.) =>   ADD CONSTRAINT main_id_cons2
db2 (cont.) =>     FOREIGN KEY (main_id)
db2 (cont.) =>       REFERENCES  test_main2(id);
DB21034E  该命令被当作 SQL 语句来处理，因为它是无效的"命令行处理器"命令。在
SQL 处理期间，它返回：
SQL0573N  在约束 "MAIN_ID_CONS2" 的引用子句中指定的列列表不标识父表或昵称
"WZQ.TEST_MAIN2" 的唯一约束。  SQLSTATE=42890
 
db2 => ALTER TABLE test_main2
db2 (cont.) =>   ADD UNIQUE (id);
DB20000I  SQL 命令成功完成。
 
db2 => ALTER TABLE test_sub2
db2 (cont.) =>   ADD CONSTRAINT main_id_cons2
db2 (cont.) =>     FOREIGN KEY (main_id)
db2 (cont.) =>       REFERENCES  test_main2(id);
DB20000I  SQL 命令成功完成。
 
 
 

启用/禁用 

当遇到批量数据导入的时候，如果外键处于有效的状态。
那么要求 数据导入的程序， 必须先导入主表的数据，然后再导入子表的数据。
如果表多，且关系复杂的话，会增加很大的工作量。
可以在数据导入以前， 先暂时禁用外键约束。
子表、主表数据都导入完毕后， 再启用外键约束。

以下测试环境为 初始的创建表、初始数据的情况。

 

db2 => ALTER TABLE test_sub ADD CONSTRAINT main_id_cons  FOREIGN KEY (main_id)
REFERENCES  test_main;
DB20000I  SQL 命令成功完成。

db2 => INSERT INTO test_sub (ID, MAIN_ID, VALUE) VALUES (3, 3, 'THREETHREE');
DB21034E  该命令被当作 SQL 语句来处理，因为它是无效的“命令行处理器”命令。在
SQL 处理期间，它返回：
SQL0530N  FOREIGN KEY "TEST.TEST_SUB.MAIN_ID_CONS"
的插入或更新值不等于父表的任何父键值。  SQLSTATE=23503

db2 =>ALTER TABLE test_sub ALTER FOREIGN KEY main_id_cons NOT ENFORCED;
DB20000I  SQL 命令成功完成。

db2 => INSERT INTO test_sub (ID, MAIN_ID, VALUE) VALUES (3, 3, 'THREETHREE');
DB20000I  SQL 命令成功完成。
db2 => INSERT INTO test_main(id, value) VALUES (3, 'THREE');
DB20000I  SQL 命令成功完成。

db2 => ALTER TABLE test_sub ALTER FOREIGN KEY main_id_cons ENFORCED;
DB20000I  SQL 命令成功完成。
db2 =>
 
db2 => DELETE test_main  WHERE ID = 1;
DB21034E  该命令被当作 SQL 语句来处理，因为它是无效的“命令行处理器”命令。在
SQL 处理期间，它返回：
SQL0532N  因为关系 "TEST.TEST_SUB.MAIN_ID_CONS" 限制删除，所以不能删除父行。
SQLSTATE=23504
　　最后提示三种情况:
　　第一:
整性的英语单词是integrity，所以这个临时关闭外键约束让你畅快导入的命令是：
　　set integrity for table_name off 
set integrity for table_name foreign key immediate unchecked 
　　第二:
　　除了这种方式，你还可以使用alter语句修改constraint，使它不执行，这样也能达到这个目的：
ALTER TABLE <table-name> ALTER FOREIGN KEY <constraint-name> NOT ENFORCED  
数据导入完成后你需要它正常工作，保证数据的完整性：
ALTER TABLE <table-name> ALTER FOREIGN KEY <constraint-name> ENFORCED 
　　第三:
　　操作DML之前
ALTER TABLE TABLENAME DISABLE CONSTRAINT CONSTRAINTNAME;
　　操作完DML以后
ALTER TABLE TABLENAME ENABLE CONSTRAINT CONSTRAINTNAME;