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

会计考友

Oracle的外键用来限制子表中参考的字段的值，必须在主表中存在。而且在主表的记录发生变化导致外键参考唯一约束值发生了变化时，定义了一系列的动作。
　　前面几篇文章介绍了Oracle所支持的3种约束行为NO ACTION、DELETE SET NULL和DELETE CASCADE.至于SQL标准中定义的其他操作，Oracle只能通过触发器来实现，这里给出一个简单的SET DEFAULT操作的例子。
　　SQL> DROP TABLE T_C;
3 q; A" C( m. |　　表已删除。
　　SQL> DROP TABLE T_P;
! ?) s' F, V  {8 b; Y) m3 x　　表已删除。
3 T: g7 Z5 n9 _　　SQL> CREATE TABLE T_P (ID NUMBER, NAME VARCHAR2(30));
) X$ V: w# q  i  ~# s; J: U/ }　　表已创建。
　　SQL> ALTER TABLE T_P ADD PRIMARY KEY (ID);
  }0 ~4 M0 e: w) z% T4 j" l　　表已更改。
　　SQL> CREATE TABLE T_C (ID NUMBER, FID NUMBER, NAME VARCHAR2(30));
; A% B6 n; Y; k. @　　表已创建。
　　SQL> ALTER TABLE T_C ADD CONSTRAINT FK_T_C
　　2 FOREIGN KEY (FID)
0 Q% W" D: }7 l3 t' U# [　　3 REFERENCES T_P (ID);
) K% I( A2 U2 J) v' t  }　　表已更改。
　　SQL> INSERT INTO T_P VALUES (1, ’A’);
1 H  k& {3 h+ l7 k( n& U　　已创建 1 行。
　　SQL> INSERT INTO T_P VALUES (2, ’B’);
0 M4 _1 A+ D! g+ r/ Y% A) ?　　已创建 1 行。
: k2 \5 g: H, b8 {! H  m; A0 y　　SQL> INSERT INTO T_C VALUES (1, 1, ’A’);
/ Z. W! q# \6 ?. q2 ~1 x  c　　已创建 1 行。
  t+ Z  @+ m  t: R* L. O　　SQL> COMMIT;
- Y2 k- P, e1 f1 _& e) P/ {* o, q/ r　　提交完成。
8 R$ J' g9 l9 _& D　　检查一下直接更新操作：
4 H) y- T, A' `$ N3 l5 c　　SQL> SELECT * FROM T_P;
$ m: x1 v& K$ [) \　　ID NAME
　　---------- ------------------------------
　　1 A
* @+ U$ X% v! t0 c. s& p. Z3 t+ @) l　　2 B
　　SQL> SELECT * FROM T_C;
9 s1 B4 A. C/ j: m' L/ C( n# q　　ID FID NAME
　　---------- ---------- ------------------------------
' {( b0 Q$ S( `& d1 E6 U4 z: s& a　　1 1 A
　　SQL> UPDATE T_P SET ID = 3 WHERE ID = 1;
6 C2 N. m$ s0 S7 A　　UPDATE T_P SET ID = 3 WHERE ID = 1

会计考友

　　*第 1 行出现错误:
5 M' k+ |& C- D& T　　ORA-02292: 违反完整约束条件 (YANGTK.FK_T_C) - 已找到子记录日志
　　下面构造一个触发器：
7 Z; @, i1 Q; |　　SQL> CREATE OR REPLACE TRIGGER T_P_SET_DEFAULT
7 A' U3 j" e8 w; h( {( P　　2 BEFORE DELETE OR UPDATE OF ID ON T_P
( a! x# |$ F) G! c" c　　3 FOR EACH ROW
　　4 WHEN (NVL(NEW.ID, -1) != NVL(OLD.ID, -1))
% k+ x; f9 v1 s4 h　　5 DECLARE
　　6 V_STR VARCHAR2(32767);
( O& i4 i: s6 |7 E　　7 BEGIN
　　8 FOR I IN (SELECT OWNER, TABLE_NAME, CONSTRAINT_NAME
& f" Z- ?( M* o0 b1 u' T, r　　9 FROM ALL_CONSTRAINTS
" [$ x7 S/ W( B5 B　　10 WHERE CONSTRAINT_TYPE = ’R’
6 W+ |5 Y9 t  N8 I" S2 @　　11 AND R_OWNER = ’YANGTK’
0 h6 B$ G6 V5 A7 C3 q4 N　　12 AND R_CONSTRAINT_NAME IN
/ `5 T& m& q% q! n, H0 `6 F　　13 (SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME = ’T_P’))
　　14 LOOP
+ P1 f. r* K6 k　　15 V_STR := ’UPDATE ’ || I.TABLE_NAME || ’ SET ’;
　　16 FOR J IN (SELECT A.COLUMN_NAME, B.DATA_DEFAULT
/ ?! L+ R" x( b　　17 FROM ALL_CONS_COLUMNS A, ALL_TAB_COLUMNS B
7 @0 @6 |# U- g7 G) `1 P　　18 WHERE A.OWNER = I.OWNER
　　19 AND B.OWNER = I.OWNER
　　20 AND A.TABLE_NAME = I.TABLE_NAME
　　21 AND B.TABLE_NAME = I.TABLE_NAME
! c3 s* X9 n$ R4 ]  `& V　　22 AND A.COLUMN_NAME = B.COLUMN_NAME
# P+ c& i. [; y( x3 Z　　23 AND CONSTRAINT_NAME = I.CONSTRAINT_NAME)
　　24 LOOP
6 j0 Q' e) p+ j* ~2 _+ l: s　　25 V_STR := V_STR || J.COLUMN_NAME || ’ = ’ || J.DATA_DEFAULT
$ W. N! }* z# n5 _& o7 ~; C　　26 || ’ WHERE ’ || J.COLUMN_NAME || ’ = ’ || :OLD.ID;
, w4 k! \2 M) G/ s　　27 END LOOP;
　　28 EXECUTE IMMEDIATE V_STR;
　　29 END LOOP;
　　30 END;
) a: [3 [- j. S: j# ]5 N6 N3 d　　31 /
3 L) X! Y+ L* c; x　　触发器已创建
9 F+ e4 S3 t- M$ D3 C# O: m　　SQL> ALTER TABLE T_C MODIFY FID DEFAULT 2;
! V+ _! a# E) o: [0 B! A　　表已更改。
5 c4 a$ r& P( N+ k5 U　　SQL> SELECT * FROM T_P;
　　ID NAME
. }4 b/ Y6 G. L- ~1 r) t" c　　---------- ------------------------------
　　1 A
　　2 B
; c' k/ R$ u7 f, R6 l. g- d  o　　SQL> SELECT * FROM T_C;
　　ID FID NAME
2 X  y: t' i% r% C$ Z　　---------- ---------- ------------------------------
　　1 1 A
　　SQL> UPDATE T_P SET ID = 3 WHERE ID = 1;
; T2 K. ?) i5 }! C　　已更新 1 行。
　　SQL> SELECT * FROM T_P;
5 S1 `: n$ V+ K2 P( B$ K3 P　　ID NAME
　　---------- ------------------------------
6 C; ]9 h/ B6 Q7 w" _6 u% v; o　　3 A
0 a1 z, N/ e  r0 O" E, m2 q　　2 B
　　SQL> SELECT * FROM T_C;
　　ID FID NAME
) w' }- K4 i7 {; G6 p8 C0 l　　---------- ---------- ------------------------------
　　1 2 A
% ?' r! c- f  p" |! w　　下面检查一下DELETE操作是否有效：
4 a& F3 _7 j" j8 ?5 g! Y' X$ T+ H3 E　　SQL> UPDATE T_C SET FID = 3;
* H0 m- K% y6 k+ j/ \/ l( w' S　　已更新 1 行。
1 b1 I2 ]: \" Y　　SQL> SELECT * FROM T_C;
　　ID FID NAME
) B' J3 ^+ M0 e% \5 T/ O7 Z. ~　　---------- ---------- -----------------------------
% A& Y4 }# `. k: |  }1 I/ A* c　　1 3 A
  d. P2 Y: N& a  R4 ?　　SQL> DELETE T_P WHERE ID = 3;
　　已删除 1 行。
2 {& m# v) ^( x# _6 Q　　SQL> SELECT * FROM T_P;
$ m( m; X3 `% `- h' T0 b　　ID NAME
　　---------- ------------------------------
( s/ X* [1 U, u) Q  P) _" h% K　　2 B
$ V3 F9 K8 P3 ~) o5 B' T7 _3 C/ p　　SQL> SELECT * FROM T_C;
& U3 G, U9 S" v1 C: p- ]　　ID FID NAME
# U5 G  p$ T6 [! I8 b　　---------- ---------- -----------------------------
2 U8 S* V5 a% x　　1 2 A