ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 小议Oracle外键约束修改行为

小议Oracle外键约束修改行为

原创 Linux操作系统 作者:zwc1083 时间:2009-02-23 15:25:42 0 删除 编辑

Oracle的外键用来限制子表中参考的字段的值,必须在主表中存在。而且在主表的记录发生变化导致外键参考唯一约束值发生了变化时,定义了一系列的动作。


SQL92标准中定义了几种外键改变后,如何处理子表记录的动作,其中包括:

限制Restrict:这种方式不允许对被参考的记录的键值执行更新或删除的操作;

置为空Set to null:当参考的数据被更新或者删除,那么所有参考它的外键值被置为空;

置为默认值Set to default:当参考的数据被更新或者删除,那么所有参考它的外键值被置为一个默认值;

级联Cascade:当参考的数据被更新,则参考它的值同样被更新,当参考的数据被删除,则参考它的子表记录也被删除;

不做操作No action:这种方式不允许更新或删除被参考的数据。和限制方式的区别在于,这种方式的检查发生在语句执行之后。Oracle默认才会的方式就是这种方式。

Oracle明确支持的方式包括No actionSet to nullCascade。对于Set to DefaultRestrictOracle的约束类型并不直接支持,不过可以通过触发器来实现。

简单看一下Oracle的默认处理方式No action

SQL> CREATE TABLE T_P (ID NUMBER, NAME VARCHAR2(30));

表已创建。

SQL> ALTER TABLE T_P ADD PRIMARY KEY (ID);

表已更改。

SQL> CREATE TABLE T_C (ID NUMBER, FID NUMBER, NAME VARCHAR2(30));

表已创建。

SQL> ALTER TABLE T_C ADD CONSTRAINT FK_T_C
2 FOREIGN KEY (FID)
3 REFERENCES T_P (ID);

表已更改。

SQL> INSERT INTO T_P VALUES (1, 'A');

已创建 1 行。

SQL> INSERT INTO T_P VALUES (2, 'B');

已创建 1 行。

SQL> INSERT INTO T_C VALUES (1, 1, 'A');

已创建 1 行。

SQL> COMMIT;

提交完成。

对于No Action操作而言,如果主键的记录被外键所参考,那么主键记录是无法更新或删除的。

SQL> DELETE T_P WHERE ID = 1;
DELETE T_P WHERE ID = 1
*
1 行出现错误:
ORA-02292:
违反完整约束条件 (YANGTK.FK_T_C) - 已找到子记录日志


SQL> UPDATE T_P SET ID = 3 WHERE ID = 1;
UPDATE T_P SET ID = 3 WHERE ID = 1
*
1 行出现错误:
ORA-02292:
违反完整约束条件 (YANGTK.FK_T_C) - 已找到子记录日志


SQL> DELETE T_P WHERE ID = 2;

已删除 1 行。

不过No Action又和Restrict操作有所区别,No Action允许用户执行语句,在语句执行之后,或者事务结束的时候才会检查是否违反约束。而Restrict只有检测到有外键参考主表的记录,就不允许删除和更新的操作执行了。

这也使得No Action操作支持延迟约束:

SQL> ALTER TABLE T_C DROP CONSTRAINT FK_T_C;

表已更改。

SQL> ALTER TABLE T_C ADD CONSTRAINT FK_T_C
2 FOREIGN KEY (FID)
3 REFERENCES T_P (ID)
4 DEFERRABLE INITIALLY DEFERRED;

表已更改。

SQL> SELECT * FROM T_P;

ID NAME
---------- ------------------------------
1 A

SQL> SELECT * FROM T_C;

ID FID NAME
---------- ---------- ------------------------------
1 1 A

SQL> DELETE T_P WHERE ID = 1;

已删除 1 行。

SQL> INSERT INTO T_P VALUES (1, 'A');

已创建 1 行。

SQL> COMMIT;

提交完成。

上一篇描述了Oracle外键处理默认操作:No Action,这里简单介绍一下SET TO NULL操作。还是利用前面例子的表,不过约束需要重建。

SQL> DROP TABLE T_C;

表已删除。

SQL> DROP TABLE T_P;

表已删除。

SQL> CREATE TABLE T_P (ID NUMBER, NAME VARCHAR2(30));

表已创建。

SQL> ALTER TABLE T_P ADD PRIMARY KEY (ID);

表已更改。

SQL> CREATE TABLE T_C (ID NUMBER, FID NUMBER, NAME VARCHAR2(30));

表已创建。

SQL> ALTER TABLE T_C ADD CONSTRAINT FK_T_C
2 FOREIGN KEY (FID)
3 REFERENCES T_P (ID)
4 ON DELETE SET NULL;

表已更改。

SQL> INSERT INTO T_P VALUES (1, 'A');

已创建 1 行。

SQL> INSERT INTO T_P VALUES (2, 'B');

已创建 1 行。

SQL> INSERT INTO T_C VALUES (1, 1, 'A');

已创建 1 行。

SQL> INSERT INTO T_C VALUES (2, 2, 'B');

已创建 1 行。

SQL> INSERT INTO T_C VALUES (3, 1, 'C');

已创建 1 行。

SQL> COMMIT;

提交完成。

下面检查一下DELETE SET NULL是如何工作的:

SQL> SELECT * FROM T_P;

ID NAME
---------- ------------------------------
1 A
2 B

SQL> SELECT * FROM T_C;

ID FID NAME
---------- ---------- ------------------------------
1 1 A
2 2 B
3 1 C

SQL> DELETE T_P WHERE ID = 2;

已删除 1 行。

SQL> SELECT * FROM T_C;

ID FID NAME
---------- ---------- ------------------------------
1 1 A
2 B
3 1 C

SQL> UPDATE T_P SET ID = 3;
UPDATE T_P SET ID = 3
*
1 行出现错误:
ORA-02292:
违反完整约束条件 (YANGTK.FK_T_C) - 已找到子记录日志

可以看到这个Set no null的操作和语法中的名称一样,只对DELETE操作有效,而对于UPDATE操作无效。

这个约束操作还有一个前提,就是要求子表的外键列允许为空,否则对主表的DELETE操作会报错:

SQL> SELECT * FROM T_C;

ID FID NAME
---------- ---------- ------------------------------
1 1 A
2 B
3 1 C

SQL> DELETE T_C WHERE ID = 2;

已删除 1 行。

SQL> ALTER TABLE T_C MODIFY FID NOT NULL;

表已更改。

SQL> DELETE T_P;
DELETE T_P
*
1 行出现错误:
ORA-01407:
无法更新 ("YANGTK"."T_C"."FID") NULL

从这里也可以看到,虽然Oracle支持Set to null,但是只是实现了DELETE语句,而没有实现UPDATE语句,这和SQL标准的定义还是有区别的。

上一篇描述了Oracle外键处理操作:SET TO NULL,这里简单介绍一下CASCADE操作。还是利用前面例子的表,不过约束需要重建。

SQL> DROP TABLE T_C;

表已删除。

SQL> DROP TABLE T_P;

表已删除。

SQL> CREATE TABLE T_P (ID NUMBER, NAME VARCHAR2(30));

表已创建。

SQL> ALTER TABLE T_P ADD PRIMARY KEY (ID);

表已更改。

SQL> CREATE TABLE T_C (ID NUMBER, FID NUMBER, NAME VARCHAR2(30));

表已创建。

SQL> ALTER TABLE T_C ADD CONSTRAINT FK_T_C
6s7kfso#]14876437  2  FOREIGN KEY (FID)
I4b%M:F1Uo14876437  3  REFERENCES T_P (ID)ITPUB个人空间 j%z%X*F j6i"Xi _&k
  4  ON DELETE CASCADE;

表已更改。

SQL> INSERT INTO T_P VALUES (1, 'A');

已创建1行。

SQL> INSERT INTO T_P VALUES (2, 'B');

已创建1行。

SQL> INSERT INTO T_C VALUES (1, 1, 'A');

已创建1行。

SQL> INSERT INTO T_C VALUES (2, 2, 'B');

已创建1行。

SQL> INSERT INTO T_C VALUES (3, 1, 'C');

已创建1行。

SQL> COMMIT;

提交完成。

下面看看CASCADE是如何工作的:

SQL> SELECT * FROM T_P;

        ID NAME
;}{ y(pu%|"I14876437---------- ------------------------------
[O*C;`'Qb$s14876437        1 AITPUB个人空间$rU7RR)S C
         2 B

SQL> SELECT * FROM T_C;

        ID        FID NAMEITPUB个人空间|g mFP9J
---------- ---------- ------------------------------ITPUB个人空间FP[U{6@"}Od gJ
         1         1 AITPUB个人空间?.P;V?7@`!MQ8p(Z
         2          2 BITPUB个人空间j^&S:L6fre |M*H
         3         1 C

SQL> DELETE T_P WHERE ID = 2;

已删除1行。

SQL> SELECT * FROM T_C;

        ID        FID NAME
3J([4pL k}?p^A14876437---------- ---------- ------------------------------ITPUB个人空间p0Xen(pWd UA zT9~
         1         1 A
1i;s1YPY9^M14876437         3         1 C

SQL> UPDATE T_P SET ID = 3 WHERE ID = 1;ITPUB个人空间8x|-f.O$oW!K.VRWS
UPDATE T_P SET ID = 3 WHERE ID = 1ITPUB个人空间+X oK7ExL Eu4M
*ITPUB个人空间-Yb-~D)@
1行出现错误:ITPUB个人空间L0g|~ |5PwSn
ORA-02292:
违反完整约束条件(YANGTK.FK_T_C) -已找到子记录日志

可以看到,和SET TO NULL约束一样,CASCADE操作也是只对DELETE语句有效,而对于UPDATE语句不起作用。

另外提一句,和SET TO NULL不同,CASCADE具有传递性,主表删除一条记录,会导致子表参考的记录被删除,而子表的记录被删除又会导致子表的子表记录被删除:

SQL> ALTER TABLE T_C ADD PRIMARY KEY (ID);

表已更改。

SQL> CREATE TABLE T_C_C (ID NUMBER PRIMARY KEY, FID NUMBER,ITPUB个人空间B `"u(RX.p*Q
  2  CONSTRAINT FK_T_C_C FOREIGN KEY (FID) REFERENCES T_C(ID) ON DELETE CASCADE);

表已创建。

SQL> INSERT INTO T_C_C VALUES (1, 1);

已创建1行。

SQL> CREATE TABLE T_C_C_C (ID NUMBER PRIMARY KEY, FID NUMBER,ITPUB个人空间.C4S-Co3|U[
  2  CONSTRAINT FK_T_C_C_C FOREIGN KEY (FID) REFERENCES T_C_C(ID) ON DELETE CASCADE);

表已创建。

SQL> INSERT INTO T_C_C_C VALUES (1, 1);

已创建1行。

SQL> COMMIT;

提交完成。

下面看看级联删除的效果:

SQL> SELECT * FROM T_P;

        ID NAME
P/L+DR$A8c!J4K l#jT14876437---------- ------------------------------ITPUB个人空间B2l2SV8]*p o
        1 A

SQL> SELECT * FROM T_C;

        ID        FID NAME
5I(U-fv%n,ik ju(o0Q14876437---------- ---------- ------------------------------ITPUB个人空间-SX/w b b1R6~ ~C
         1         1 A
%q8?$y^E9J14876437         3         1 C

SQL> SELECT * FROM T_C_C;

        ID        FID
h t:Oq0S([ t&t:_(g4o-B14876437---------- ----------ITPUB个人空间T:{3x9T'Tm |R
         1          1

SQL> SELECT * FROM T_C_C_C;

        ID        FIDITPUB个人空间)K.I1p9q#U%L#r } P0}\
---------- ----------
,?*L]7j*m2H1Y7]14876437         1          1

SQL> DELETE T_P;

已删除1行。

SQL> SELECT * FROM T_P;

未选定行

SQL> SELECT * FROM T_C;

未选定行

SQL> SELECT * FROM T_C_C;

未选定行

SQL> SELECT * FROM T_C_C_C;

未选定行

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/14876437/viewspace-557455/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-02-16

  • 博文量
    91
  • 访问量
    43591