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

会计考友

Oracle的外键用来限制子表中参考的字段的值，必须在主表中存在。而且在主表的记录发生变化导致外键参考唯一约束值发生了变化时，定义了一系列的动作。
　　前面几篇文章介绍了Oracle所支持的3种约束行为NO ACTION、DELETE SET NULL和DELETE CASCADE.至于SQL标准中定义的其他操作，Oracle只能通过触发器来实现，这里给出一个简单的UPDATE SET NULL操作的例子。
　　SQL> DROP TABLE T_C;
　　表已删除。
+ W" U7 t  M! t7 v; K9 y: @　　SQL> DROP TABLE T_P;
　　表已删除。
0 G! S  @* q2 m* C" L! p" o( U' |　　SQL> CREATE TABLE T_P (ID NUMBER, NAME VARCHAR2(30));
　　表已创建。
: f5 M" G) Z( u/ r3 |7 O8 ~　　SQL> ALTER TABLE T_P ADD PRIMARY KEY (ID);
　　表已更改。
　　SQL> CREATE TABLE T_C (ID NUMBER, FID NUMBER, NAME VARCHAR2(30));
% X* H. s# i+ q) e　　表已创建。
+ m/ V( X) U( ^3 \　　SQL> ALTER TABLE T_C ADD CONSTRAINT FK_T_C
' x3 O4 k# r& X7 i" R　　2 FOREIGN KEY (FID)
　　3 REFERENCES T_P (ID);
　　表已更改。
) h" I( Y6 I9 R- i2 M9 }; Z　　SQL> INSERT INTO T_P VALUES (1, ’A’);
　　已创建 1 行。
. l* I+ J+ V5 ], l$ l　　SQL> INSERT INTO T_P VALUES (2, ’B’);
　　已创建 1 行。
2 a! [  ]) b7 r3 {　　SQL> INSERT INTO T_C VALUES (1, 1, ’A’);
2 s* d" h' _+ j1 c8 J4 D　　已创建 1 行。
　　SQL> COMMIT;
　　提交完成。
. ]0 `- E# M! [6 j　　检查一下直接更新操作：
3 {. ~$ d. I4 F# I5 g4 O' {0 n　　SQL> SELECT * FROM T_P;
$ D5 F9 l( b& Y2 ]3 Q/ y; S　　ID NAME
　　---------- ------------------------------
5 X/ j1 x9 X% w" ^　　1 A
　　2 B
; l: V8 V  _! C- {: H　　SQL> SELECT * FROM T_C;
5 I) R" u7 n4 Q/ T3 |; y! b　　ID FID NAME
　　---------- ---------- ------------------------------
　　1 1 A
　　SQL> UPDATE T_P SET ID = 3 WHERE ID = 1;
  [! ~9 I( w, }( A1 d* s' z6 G　　UPDATE T_P SET ID = 3 WHERE ID = 1

会计考友

　　*第 1 行出现错误:
" t7 q& s& F& u+ f% D/ _4 d( x+ U　　ORA-02292: 违反完整约束条件 (YANGTK.FK_T_C) - 已找到子记录日志
　　下面构造一个触发器：
  D4 M; E, e+ Y　　SQL> CREATE OR REPLACE TRIGGER T_P_UPDATE_SET_NULL
: F3 B: J3 K) M) z: q' b, O/ I　　2 BEFORE UPDATE ON T_P
8 Z0 s0 M! j& x$ D　　3 FOR EACH ROW
　　4 DECLARE
, F3 D' ~) m: s7 U2 h& a  R　　5 V_STR VARCHAR2(32767);
6 ?1 X$ g3 ?# D+ _( c　　6 BEGIN
　　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
　　10 WHERE CONSTRAINT_TYPE = ’R’
1 k+ {/ W# H$ i8 \6 z  v7 T# O4 B7 T　　11 AND R_OWNER = ’YANGTK’
0 v0 e) P( [8 X! j+ o+ E! W　　12 AND R_CONSTRAINT_NAME IN
　　13 (SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME = ’T_P’))
　　14 LOOP
7 s6 L, s* F  E5 K* a　　15 V_STR := ’UPDATE ’ || I.TABLE_NAME || ’ SET ’;
　　16 FOR J IN (SELECT COLUMN_NAME FROM ALL_CONS_COLUMNS
4 x, b, R- ?; c& i3 K　　17 WHERE OWNER = I.OWNER
　　18 AND TABLE_NAME = I.TABLE_NAME
5 M6 S1 p) h+ H% D+ E* j; o　　19 AND CONSTRAINT_NAME = I.CONSTRAINT_NAME)
7 z5 L3 |; E7 h　　20 LOOP
　　21 V_STR := V_STR || J.COLUMN_NAME || ’ = NULL ’
! l" d* O/ }2 z5 n- J　　22 || ’WHERE ’ || J.COLUMN_NAME || ’ = ’ || :OLD.ID;
4 h. T& {0 {' u5 I+ k, V　　23 END LOOP;
　　24 EXECUTE IMMEDIATE V_STR;
9 s( ^. b% ^3 L% p5 }　　25 END LOOP;
2 u- a2 E% [6 u) F0 \6 g% B' d( ?　　26 END IF;
　　27 END;
　　28 /
5 v* O! M) w& b. \0 e　　触发器已创建
　　SQL> SELECT * FROM T_P;
　　ID NAME
* Q( W5 {  [: l1 F" ^  j- ?　　---------- ------------------------------
9 W( G+ _) o) ?( n9 o9 S　　1 A
' ~$ l, K4 C: K3 k3 f　　2 B
  D! c. G. K: a+ {" k　　SQL> SELECT * FROM T_C;
$ Q4 `  E7 N! R* Z) b" t: h　　ID FID NAME
1 H8 t& t2 t0 b% C! l& ?; k　　---------- ---------- ------------------------------
　　1 1 A
4 l& b8 k6 C2 i, u8 [　　SQL> UPDATE T_P SET ID = 3 WHERE ID = 1;
4 ~( S# }2 R' W2 b+ ~/ H　　已更新 1 行。
　　SQL> SELECT * FROM T_P;
　　ID NAME
( T# {6 y! u* F9 u; g　　---------- ------------------------------
! W% g0 l5 v0 S( q/ z　　3 A
　　2 B
　　SQL> SELECT * FROM T_C;
　　ID FID NAME
　　---------- ---------- ------------------------------
　　1 A
) Q- O' l: w) E$ b8 T# u　　UPDATE SET NULL操作其实和UPDATE CASCADE很相似，不同之处无非是一个置为NULL，另一个置为主键的新值。