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

会计考友

Oracle的外键用来限制子表中参考的字段的值，必须在主表中存在。而且在主表的记录发生变化导致外键参考唯一约束值发生了变化时，定义了一系列的动作。
　　前面几篇文章介绍了Oracle所支持的3种约束行为NO ACTION、DELETE SET NULL和DELETE CASCADE.至于SQL标准中定义的其他操作，Oracle只能通过触发器来实现，这里给出一个简单的UPDATE SET NULL操作的例子。
　　SQL> DROP TABLE T_C;
# }! {! o+ K4 N! O　　表已删除。
* d9 e4 \( Y& Y　　SQL> DROP TABLE T_P;
　　表已删除。
　　SQL> CREATE TABLE T_P (ID NUMBER, NAME VARCHAR2(30));
0 [8 }7 I$ k- I- W! Y7 V5 E. f& Z　　表已创建。
　　SQL> ALTER TABLE T_P ADD PRIMARY KEY (ID);
: _7 I1 r& V- S' }) d　　表已更改。
5 r- o1 X! {( h* D2 L( @　　SQL> CREATE TABLE T_C (ID NUMBER, FID NUMBER, NAME VARCHAR2(30));
　　表已创建。
　　SQL> ALTER TABLE T_C ADD CONSTRAINT FK_T_C
' u& D) B, S+ ~$ E& ^* `( ]* Y　　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’);
0 j+ l2 b% i$ g　　已创建 1 行。
/ H" h8 v, W' s; [) @6 a7 N6 y( F; m　　SQL> INSERT INTO T_C VALUES (1, 1, ’A’);
6 o7 F1 s# y$ f　　已创建 1 行。
　　SQL> COMMIT;
　　提交完成。
/ a$ u+ w" `: g' L$ r　　检查一下直接更新操作：
& w8 P& y: s  ~7 k# l9 u　　SQL> SELECT * FROM T_P;
  q+ R* Q% `: o) D. ~  `4 u$ B　　ID NAME
　　---------- ------------------------------
* f+ H+ J6 W$ S8 F% A; L　　1 A
! B# v% O$ [: F, `& z& ?　　2 B
  b7 _. R7 |: f, c( c6 ^  y* Y　　SQL> SELECT * FROM T_C;
　　ID FID NAME
　　---------- ---------- ------------------------------
　　1 1 A
8 _; W- N4 L) J/ U4 {6 P7 a+ \　　SQL> UPDATE T_P SET ID = 3 WHERE ID = 1;
　　UPDATE T_P SET ID = 3 WHERE ID = 1

会计考友

　　*第 1 行出现错误:
5 \" h: V# I9 Q. e( a. K　　ORA-02292: 违反完整约束条件 (YANGTK.FK_T_C) - 已找到子记录日志
　　下面构造一个触发器：
1 M6 v' R' L$ K+ g% Z9 M/ ^　　SQL> CREATE OR REPLACE TRIGGER T_P_UPDATE_SET_NULL
* H% x' ^. H. J) E8 K' |　　2 BEFORE UPDATE ON T_P
1 p0 i2 v+ E  n6 k　　3 FOR EACH ROW
1 ~& c! g9 X6 P; T9 ^! M1 r: y　　4 DECLARE
1 l: i: e! i1 g　　5 V_STR VARCHAR2(32767);
& i. J0 C8 F( \. e" T' T4 b" A　　6 BEGIN
) l+ }0 z+ F+ G4 p　　7 IF NVL(:NEW.ID, -1) != NVL(:OLD.ID, -1) THEN
/ p- p3 U  i# G0 s7 v: Q6 N2 C　　8 FOR I IN (SELECT OWNER, TABLE_NAME, CONSTRAINT_NAME
* x; R4 K1 \/ F; F　　9 FROM ALL_CONSTRAINTS
　　10 WHERE CONSTRAINT_TYPE = ’R’
　　11 AND R_OWNER = ’YANGTK’
　　12 AND R_CONSTRAINT_NAME IN
　　13 (SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME = ’T_P’))
0 M$ b# k5 `7 S1 n5 L. G　　14 LOOP
　　15 V_STR := ’UPDATE ’ || I.TABLE_NAME || ’ SET ’;
4 y# X3 V( E  c9 N! l　　16 FOR J IN (SELECT COLUMN_NAME FROM ALL_CONS_COLUMNS
! F3 u; U% W/ E7 H8 ^　　17 WHERE OWNER = I.OWNER
　　18 AND TABLE_NAME = I.TABLE_NAME
6 v! t" h* ~1 H4 |% T8 n　　19 AND CONSTRAINT_NAME = I.CONSTRAINT_NAME)
6 O, T, C# V$ u　　20 LOOP
　　21 V_STR := V_STR || J.COLUMN_NAME || ’ = NULL ’
; g3 ]0 F2 V6 \0 V　　22 || ’WHERE ’ || J.COLUMN_NAME || ’ = ’ || :OLD.ID;
! T, V. y- t: q: C! A　　23 END LOOP;
　　24 EXECUTE IMMEDIATE V_STR;
& h% E" O3 I8 Y" ^$ {# D5 a8 ~4 }' ]0 E　　25 END LOOP;
　　26 END IF;
　　27 END;
. `" L) E" B7 o' ]! s　　28 /
　　触发器已创建
　　SQL> SELECT * FROM T_P;
　　ID NAME
, v8 b9 Y9 ?! g" ~　　---------- ------------------------------
　　1 A
+ W) E& z  g. C, n7 i! I: r　　2 B
9 I& @7 D! F& \' X' G, h　　SQL> SELECT * FROM T_C;
　　ID FID NAME
　　---------- ---------- ------------------------------
7 p9 X9 O9 \* @8 G" M　　1 1 A
　　SQL> UPDATE T_P SET ID = 3 WHERE ID = 1;
　　已更新 1 行。
　　SQL> SELECT * FROM T_P;
; J7 j. Q8 }# C　　ID NAME
　　---------- ------------------------------
% s9 E# |# P; V4 r) \9 g　　3 A
　　2 B
　　SQL> SELECT * FROM T_C;
, }' t/ U7 P- a　　ID FID NAME
  G2 f& x% s$ j8 K# b- p/ ^% Y　　---------- ---------- ------------------------------
　　1 A
　　UPDATE SET NULL操作其实和UPDATE CASCADE很相似，不同之处无非是一个置为NULL，另一个置为主键的新值。