一个DB2级联删除的问题的分析
一个DB2级联删除的问题:
The following statements:
CREATE TABLE t1 (col1 INT NOT NULL, PRIMARY KEY(col1));
CREATE TABLE t2 (col1 INT NOT NULL, col2 CHAR(1) NOT NULL, PRIMARY KEY (col1, col2),
FOREIGN KEY (col1) REFERENCES t1 (col1)
ON DELETE CASCADE ON UPDATE RESTRICT);
CREATE TABLE t3 (col1 INT NOT NULL, col2 INT NOT NULL, PRIMARY KEY (col1, col2),
FOREIGN KEY (col1) REFERENCES t1 (col1)
ON DELETE NO ACTION ON UPDATE RESTRICT);
INSERT INTO t1 VALUES (1), (2);
INSERT INTO t2 VALUES (1, 'a'), (1, 'b'), (2,'c');
INSERT INTO t3 VALUES (1, 100), (2, 200), (2,300);
How many rows will be deleted by the following DELETE statement? DELETE FROM t1 WHERE col1=
1;
A. 4
B. 3
C. 1
D. 0
Answer: D
解析总结:
On Delete 的选项有:
Restrict/no action/cascade/set null.
其中cascade选项指定的话,如果删除父记录,依赖于他的子记录也会自动删除,相当于级联删除.如果指定
no action和Restrict都会报错,因为还有子记录所以无法删除该记录.set null允许删除父记录并且会将
子表中与父表关联的字段设置为null,当然,前提是该字段可以为空,否则同样会报错.
(本文经过实验验证)