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

会计考友

　Oracle的外键用来限制子表中参考的字段的值，必须在主表中存在。而且在主表的记录发生变化导致外键参考唯一约束值发生了变化时，定义了一系列的动作。
* M* F! F/ E, K, ~+ S+ U+ q　　前面几篇文章介绍了Oracle所支持的3种约束行为NO ACTION、DELETE SET NULL和DELETE CASCADE.至于SQL标准中定义的其他操作，Oracle只能通过触发器来实现，这里给出一个简单的UPDATE CASCADE操作的例子。
　　SQL> DROP TABLE T_C;
4 i- i4 ?+ R$ f1 m+ t3 s. c1 a7 [　　表已删除。
　　SQL> DROP TABLE T_P;
# ?* P; K7 O* N: i2 I- ?　　表已删除。
　　SQL> CREATE TABLE T_P (ID NUMBER, NAME VARCHAR2(30));
1 ^- a5 j2 c0 F7 A. {. B3 m# z　　表已创建。
# j0 R3 Z" M: Y: {/ _9 E1 a　　SQL> ALTER TABLE T_P ADD PRIMARY KEY (ID);
　　表已更改。
# q9 e, J6 N: w, \" n　　SQL> CREATE TABLE T_C (ID NUMBER, FID NUMBER, NAME VARCHAR2(30));
( R. R& Q% F! V8 S: o# v  }　　表已创建。
　　SQL> ALTER TABLE T_C ADD CONSTRAINT FK_T_C
　　2 FOREIGN KEY (FID)
: b0 J! K) i7 s- d9 k　　3 REFERENCES T_P (ID);
　　表已更改。
　　SQL> INSERT INTO T_P VALUES (1, ’A’);
　　已创建 1 行。
　　SQL> INSERT INTO T_P VALUES (2, ’B’);
% q+ h3 u$ g$ u: T3 z* S　　已创建 1 行。
　　SQL> INSERT INTO T_C VALUES (1, 1, ’A’);
% _7 z  q2 [% e/ _4 x/ n　　已创建 1 行。
　　SQL> COMMIT;
　　提交完成。
: F' o. ~. Q! Z# X　　检查一下直接更新操作：
　　SQL> SELECT * FROM T_P;
　　ID NAME
　　---------- ------------------------------
　　1 A
9 k: T7 a1 T+ h9 h　　2 B
# I' f' Q6 s7 X- k2 p) y　　SQL> SELECT * FROM T_C;
: A+ d2 k! W# Z7 |: h/ e4 c7 G' b　　ID FID NAME
. E/ _/ {  R: v6 Z$ }, j* _8 Q　　---------- ---------- ------------------------------
7 K. D, O0 _4 R# J　　1 1 A
　　SQL> UPDATE T_P SET ID = 3 WHERE ID = 1;
: E# a+ \* p+ \　　UPDATE T_P SET ID = 3 WHERE ID = 1
　　*第 1 行出现错误:
　　ORA-02292: 违反完整约束条件 (YANGTK.FK_T_C) - 已找到子记录日志

会计考友

　　下面构造一个触发器：
　　SQL> CREATE OR REPLACE TRIGGER T_P_UPDATE_CASCADE
, }) k$ g  B2 Q4 \4 [　　2 BEFORE UPDATE ON T_P
　　3 FOR EACH ROW
7 T: F' I9 p0 W3 p+ |- i　　4 DECLARE
3 f! a2 ]* L. E; I0 c7 P5 c　　5 V_STR VARCHAR2(32767);
) C5 r9 b% J. ?) ~　　6 BEGIN
　　7 IF NVL(:NEW.ID, -1) != NVL(:OLD.ID, -1) THEN
) A6 r4 c! P$ w9 Y　　8 FOR I IN (SELECT OWNER, TABLE_NAME, CONSTRAINT_NAME
　　9 FROM ALL_CONSTRAINTS
　　10 WHERE CONSTRAINT_TYPE = ’R’
' p- ?( H+ J0 J- D$ \　　11 AND R_OWNER = ’YANGTK’
" |7 z3 K- Y% W, S/ f8 x5 |* n- m　　12 AND R_CONSTRAINT_NAME IN
& ^! q& j4 u' {" c0 W" i　　13 (SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME = ’T_P’))
　　14 LOOP
　　15 V_STR := ’UPDATE ’ || I.TABLE_NAME || ’ SET ’;
　　16 FOR J IN (SELECT COLUMN_NAME FROM ALL_CONS_COLUMNS
　　17 WHERE OWNER = I.OWNER
　　18 AND TABLE_NAME = I.TABLE_NAME
　　19 AND CONSTRAINT_NAME = I.CONSTRAINT_NAME)
　　20 LOOP
$ e8 ~8 ]6 j/ \; g7 W! Q　　21 V_STR := V_STR || J.COLUMN_NAME || ’ = ’ || :NEW.ID
1 a' }: t' r/ u6 I3 t$ w! P　　22 || ’ WHERE ’ || J.COLUMN_NAME || ’ = ’ || :OLD.ID;
　　23 END LOOP;
1 t. c! {1 s, E　　24 EXECUTE IMMEDIATE V_STR;
( ~: E, E$ m+ |5 j7 Q　　25 END LOOP;
　　26 END IF;
. O# C( Q+ s! Y' L6 c# w5 N　　27 END;
* ^% N8 Y/ S1 D, ?　　28 /
　　触发器已创建
% V8 X2 ~& F2 _  ~5 ?　　SQL> SELECT * FROM T_P;
　　ID NAME
4 A9 A, k/ q5 q, I1 \7 F　　---------- ------------------------------
　　1 A
* P2 E" ~" U% L7 M1 b　　2 B
6 s0 s: W+ O* t: _3 {　　SQL> SELECT * FROM T_C;
' c7 h# r9 ^% ?　　ID FID NAME
! N0 [% w5 \) O1 Y　　---------- ---------- ------------------------------
/ w8 _( |* Y9 ?5 M# f( h. G　　1 1 A
　　SQL> UPDATE T_P SET ID = 3 WHERE ID = 1;
( c) G1 X0 H% z" ^　　已更新 1 行。
3 ^- y5 N+ g8 [* Q　　SQL> SELECT * FROM T_P;
　　ID NAME
　　---------- ------------------------------
) g* f7 T3 q6 u9 }　　3 A
6 ~& U; P7 @: |% F6 u& S　　2 B
　　SQL> SELECT * FROM T_C;
$ I% i! P! \6 Q/ m+ z　　ID FID NAME
　　---------- ---------- ------------------------------
: X3 H" d6 Z8 K　　1 3 A
　　采用动态SQL的好处是，即使新增了新的外键表，触发器也不用重新编辑：
　　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);
6 A7 k+ _0 G* V( b3 u　　已创建 1 行。
+ i$ p: \: }7 ?  m& x. M, Y- L　　SQL> UPDATE T_P SET ID = 1 WHERE ID = 3;
. y1 s, ]* |9 a1 J0 D/ S* ]　　已更新 1 行。
　　SQL> SELECT * FROM T_P;
6 h  o4 ?/ C1 z$ q9 G( D5 L  ]　　ID NAME
1 T6 |+ Y- q' u) l2 t　　---------- ------------------------------
　　1 A
) {8 z9 {+ I( T9 A) h　　2 B
+ q# t& {3 g0 N/ T* W) U　　SQL> SELECT * FROM T_C;
　　ID FID NAME
　　---------- ---------- ------------------------------
5 D. U7 `* C6 S( i　　1 1 A
' L- k( @# l' i3 l+ x　　SQL> SELECT * FROM T_C1;
+ P* `" }) r/ E, z　　ID FID
& d) _, K# O; s' y  U+ v1 v　　---------- ----------
　　1 1