Oracle的外键用来限制子表中参考的字段的值,必须在主表中存在。而且在主表的记录发生变化导致外键参考唯一约束值发生了变化时,定义了一系列的动作。
6 F' n8 R/ h1 ^ 这篇简单描述一下SET TO NULL操作。
. q, N4 F( K7 J5 |* a0 ^, P6 [ 上一篇描述了Oracle外键处理默认操作:No Action,这里简单介绍一下SET TO NULL操作。还是利用前面例子的表,不过约束需要重建。
" S' | A: G5 c) w SQL> DROP TABLE T_C;
% E/ R" Z8 z! F 表已删除。+ s" F3 v- Z9 g) G3 j9 K5 f0 \
SQL> DROP TABLE T_P;
- H" J a5 B. ~ D 表已删除。( j$ M1 _. \/ K" q# |
SQL> CREATE TABLE T_P (ID NUMBER, NAME VARCHAR2(30));& ~6 t2 b0 L" p" O
表已创建。
7 ~' o- j) r( c5 \ SQL> ALTER TABLE T_P ADD PRIMARY KEY (ID);0 m3 n! Z4 K( H) Q' {, B
表已更改。( c7 @: ?) f" ?4 F% n) u
SQL> CREATE TABLE T_C (ID NUMBER, FID NUMBER, NAME VARCHAR2(30));
# i1 ?8 L: D# z. ~3 J* K6 l7 Y 表已创建。# m- t$ \. h/ x; Z
SQL> ALTER TABLE T_C ADD CONSTRAINT FK_T_C
' |# A q! G0 V9 u; j) r/ K 2 FOREIGN KEY (FID)
/ P1 o6 \8 ]) S3 A 3 REFERENCES T_P (ID)0 e4 u* {+ k6 h! ?6 k% i
4 ON DELETE SET NULL;
) ~6 N9 j- o# Q 表已更改。% I( o" n7 c) g( u' K
SQL> INSERT INTO T_P VALUES (1, ’A’);% k1 f4 p. L+ P$ H. R9 A0 `7 _
已创建 1 行。
9 n/ _' j1 u, ~! k+ f+ n SQL> INSERT INTO T_P VALUES (2, ’B’);
I1 v% @: t8 ^4 a 已创建 1 行。: @( f0 v. g6 j$ H1 W
SQL> INSERT INTO T_C VALUES (1, 1, ’A’);
6 v, y/ @; N, n9 o# L- m 已创建 1 行。5 G) a. @# V5 i1 @( w% k
SQL> INSERT INTO T_C VALUES (2, 2, ’B’);' d! ]4 I6 W- N0 S/ d
已创建 1 行。2 d' O; A% T# c; [+ j- S3 I |# \
SQL> INSERT INTO T_C VALUES (3, 1, ’C’);7 i; G4 A; \9 M3 m! w! J0 \
已创建 1 行。
3 h* C. H p- ~3 }& A6 X2 T SQL> COMMIT;
$ D" E/ B! n2 W/ N- T3 \ 提交完成。
. c/ _7 a" I" ]5 i0 P 下面检查一下DELETE SET NULL是如何工作的:5 D2 F% X8 d3 H6 k; d( _' h
SQL> SELECT * FROM T_P;( T& A$ e0 i3 a) \5 V; z' ~2 w6 u' V4 K
ID NAME
4 N) M2 l! W; D/ |2 h- ` ---------- ------------------------------
" d Y0 Z5 d2 g2 I+ X 1 A- ?) j" N/ O, S% `' q& f y" |
2 B( g' M4 u# v9 q) {
SQL> SELECT * FROM T_C;
6 q0 @$ H9 O+ u$ C ID FID NAME
% p4 M9 U( k- p' z ---------- ---------- ------------------------------( V, j! o" N" v: @6 y% E; ^
1 1 A( r6 u% Z+ o+ z, p
2 2 B- H4 q5 d2 ^0 R* W& U6 y* g- K, [& x
3 1 C# l' I( F* Y9 I/ C
SQL> DELETE T_P WHERE ID = 2;% b9 O7 a3 O2 n* G
已删除 1 行。* {$ v' S( e1 t+ ^- M
SQL> SELECT * FROM T_C;( A1 I. X4 I4 M' A9 ^3 r1 M$ e
ID FID NAME
8 x$ D- ^* R* o; @6 E ---------- ---------- ------------------------------. H3 ]5 h4 }" q3 Y7 y* p& N; Y
1 1 A0 K$ m# M8 @; ?" Z% |8 F& t: a$ J7 j
2 B
: _( s# J. U: X' [: A8 [- q 3 1 C
% [# C& d0 Z* T; C- o" R0 ] SQL> UPDATE T_P SET ID = 3;
2 O. M a2 ]5 R2 Q5 A UPDATE T_P SET ID = 3
( v) k) Y8 Q# h *第 1 行出现错误:& I, N) V# K7 F0 S Y# L6 }- K5 \* d
ORA-02292: 违反完整约束条件 (YANGTK.FK_T_C) - 已找到子记录日志) i' ^' p7 \" E- L( X; E( @
可以看到这个Set no null的操作和语法中的名称一样,只对DELETE操作有效,而对于UPDATE操作无效。
; l% b: Y w, {" Q: R8 L 这个约束操作还有一个前提,就是要求子表的外键列允许为空,否则对主表的DELETE操作会报错:
% {: y Z& y9 m( W6 }- g& `- ` SQL> SELECT * FROM T_C;& `, B1 S! X& d4 h3 O$ P# H
ID FID NAME
. ]' I7 |) k- k+ h# P) E8 F ---------- ---------- ------------------------------
4 R0 f5 D. U' B/ R3 K 1 1 A: o) H; h; ~+ A
2 B
7 Z( V- ]# j5 ^3 l( J 3 1 C
" i0 s0 g! }. c* p0 r: B% ]; j! [( |. O SQL> DELETE T_C WHERE ID = 2;
7 w$ R* p$ b8 C 已删除 1 行。# D' A! g* e0 } i% W6 {
SQL> ALTER TABLE T_C MODIFY FID NOT NULL;
2 M& L! p5 A" f: D- R2 x 表已更改。( m0 c3 H6 P% J% w
SQL> DELETE T_P;* L3 Q) Q5 N4 T% O+ R) E _. E
DELETE T_P! B) W2 s* ]7 ]- b9 F- `
*第 1 行出现错误:5 Q0 ^3 W) N# U1 ^# ~/ X
ORA-01407: 无法更新 ("YANGTK"."T_C"."FID") 为 NULL
- a1 v: m/ \" d; H: n 从这里也可以看到,虽然Oracle支持Set to null,但是只是实现了DELETE语句,而没有实现UPDATE语句,这和SQL标准的定义还是有区别的。 |