Oracle辅导：Oracle外键约束修改行为（四）

会计考友

　Oracle的外键用来限制子表中参考的字段的值，必须在主表中存在。而且在主表的记录发生变化导致外键参考唯一约束值发生了变化时，定义了一系列的动作。
　　前面几篇文章介绍了Oracle所支持的3种约束行为NO ACTION、DELETE SET NULL和DELETE CASCADE.至于SQL标准中定义的其他操作，Oracle只能通过触发器来实现，这里给出一个简单的UPDATE CASCADE操作的例子。
. e0 p8 i& i& s  ~8 r; b　　SQL> DROP TABLE T_C;
　　表已删除。
4 X5 P) b6 e3 z1 q　　SQL> DROP TABLE T_P;
　　表已删除。
　　SQL> CREATE TABLE T_P (ID NUMBER, NAME VARCHAR2(30));
: s4 x2 j4 C8 E% F: n" h　　表已创建。
　　SQL> ALTER TABLE T_P ADD PRIMARY KEY (ID);
- p( w0 Z9 ~% W　　表已更改。
% b; e, k* Q5 Y$ J7 g9 l* I　　SQL> CREATE TABLE T_C (ID NUMBER, FID NUMBER, NAME VARCHAR2(30));
　　表已创建。
  t1 _" n0 ~2 v" b8 V　　SQL> ALTER TABLE T_C ADD CONSTRAINT FK_T_C
. ?: c" E( p" ^( c1 E　　2 FOREIGN KEY (FID)
& |! q. S, X3 i　　3 REFERENCES T_P (ID);
# p  S+ S! \; q3 @& o　　表已更改。
$ n9 L. D0 [4 t9 g7 |　　SQL> INSERT INTO T_P VALUES (1, ’A’);
: \% w& _5 S- |2 g; J; d' I　　已创建 1 行。
　　SQL> INSERT INTO T_P VALUES (2, ’B’);
　　已创建 1 行。
3 g7 m4 b7 P" o4 y" i　　SQL> INSERT INTO T_C VALUES (1, 1, ’A’);
9 O( B+ r0 n% u, ~) R8 c　　已创建 1 行。
" Z6 ~# y* g4 H4 Q3 d5 r　　SQL> COMMIT;
; f- J4 n! p8 T/ u' t9 z/ B- t　　提交完成。
9 I  h9 ~0 ^1 e/ w! {　　检查一下直接更新操作：
$ M/ M' L4 W$ g& l+ A　　SQL> SELECT * FROM T_P;
　　ID NAME
　　---------- ------------------------------
/ y* I4 L: }: S6 _. m8 D　　1 A
  S/ r7 R+ h) r0 n% T2 t$ w　　2 B
9 v3 }. y: M2 E7 Q1 @1 ]　　SQL> SELECT * FROM T_C;
4 r( L- c" e$ z: y$ ~9 L# {! o　　ID FID NAME
　　---------- ---------- ------------------------------
　　1 1 A
　　SQL> UPDATE T_P SET ID = 3 WHERE ID = 1;
　　UPDATE T_P SET ID = 3 WHERE ID = 1
8 c# F2 R) _, T. Z  g3 J　　*第 1 行出现错误:
　　ORA-02292: 违反完整约束条件 (YANGTK.FK_T_C) - 已找到子记录日志

会计考友

　　下面构造一个触发器：
% X& v; g* W4 I% h　　SQL> CREATE OR REPLACE TRIGGER T_P_UPDATE_CASCADE
　　2 BEFORE UPDATE ON T_P
　　3 FOR EACH ROW
　　4 DECLARE
8 o4 H7 Y2 K: e: Q3 _7 l6 Z　　5 V_STR VARCHAR2(32767);
　　6 BEGIN
; G0 |; l' c0 a　　7 IF NVL(:NEW.ID, -1) != NVL(:OLD.ID, -1) THEN
　　8 FOR I IN (SELECT OWNER, TABLE_NAME, CONSTRAINT_NAME
　　9 FROM ALL_CONSTRAINTS
1 X; o5 P( M& f　　10 WHERE CONSTRAINT_TYPE = ’R’
　　11 AND R_OWNER = ’YANGTK’
! p4 A/ w9 N  d9 D. Y, Y6 ~$ L3 s　　12 AND R_CONSTRAINT_NAME IN
+ T; g6 j/ b6 y4 c/ P  q　　13 (SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME = ’T_P’))
- T0 q, P, q3 D6 j　　14 LOOP
! R# O0 Q* z: }8 j　　15 V_STR := ’UPDATE ’ || I.TABLE_NAME || ’ SET ’;
　　16 FOR J IN (SELECT COLUMN_NAME FROM ALL_CONS_COLUMNS
8 u4 d* O4 t0 k) [- {' W, p　　17 WHERE OWNER = I.OWNER
　　18 AND TABLE_NAME = I.TABLE_NAME
　　19 AND CONSTRAINT_NAME = I.CONSTRAINT_NAME)
　　20 LOOP
, O( y3 F+ n. |- Z) s　　21 V_STR := V_STR || J.COLUMN_NAME || ’ = ’ || :NEW.ID
: h" o$ v* c$ s, e　　22 || ’ WHERE ’ || J.COLUMN_NAME || ’ = ’ || :OLD.ID;
8 Z- s4 X; k- @2 X　　23 END LOOP;
　　24 EXECUTE IMMEDIATE V_STR;
% N1 e7 V9 [5 T4 Q8 j5 q　　25 END LOOP;
　　26 END IF;
　　27 END;
　　28 /
' J6 V/ N( v# m4 V　　触发器已创建
　　SQL> SELECT * FROM T_P;
　　ID NAME
% N6 j' O) e, C2 p% x　　---------- ------------------------------
; v  k! T  j! e0 r　　1 A
6 A# G' K; u6 O  [2 O; ~8 `0 g　　2 B
5 U3 z. C5 I4 Y% i9 h　　SQL> SELECT * FROM T_C;
　　ID FID NAME
　　---------- ---------- ------------------------------
　　1 1 A
  r: S0 j  V) g% e  b: b　　SQL> UPDATE T_P SET ID = 3 WHERE ID = 1;
　　已更新 1 行。
& j9 P' i. }8 k+ E　　SQL> SELECT * FROM T_P;
# A3 J9 H. U4 \/ y) K) J) Q　　ID NAME
　　---------- ------------------------------
　　3 A
　　2 B
　　SQL> SELECT * FROM T_C;
　　ID FID NAME
　　---------- ---------- ------------------------------
9 ^1 Q% X- C) d- G8 v/ n" Z　　1 3 A
　　采用动态SQL的好处是，即使新增了新的外键表，触发器也不用重新编辑：
# i) g/ ~* I+ b5 U0 L3 |　　SQL> CREATE TABLE T_C1 (ID NUMBER, FID NUMBER, CONSTRAINT FK_T_C1
　　2 FOREIGN KEY (FID) REFERENCES T_P);
　　表已创建。
　　SQL> INSERT INTO T_C1 VALUES (1, 3);
7 u, T! r6 `; E+ J# B& y　　已创建 1 行。
　　SQL> UPDATE T_P SET ID = 1 WHERE ID = 3;
　　已更新 1 行。
　　SQL> SELECT * FROM T_P;
　　ID NAME
　　---------- ------------------------------
" F8 C  R  \. f9 a! }& i　　1 A
　　2 B
, I* P9 X" f+ r' p　　SQL> SELECT * FROM T_C;
　　ID FID NAME
, y, H. \0 U$ d0 h7 S　　---------- ---------- ------------------------------
　　1 1 A
　　SQL> SELECT * FROM T_C1;
- v* Z( l# S7 }& v8 ~0 N　　ID FID
. J0 l% p9 G& ^4 N# |1 H　　---------- ----------
# W- b; Y+ r5 A* W6 {' K　　1 1