a我考网

 找回密码
 立即注册

QQ登录

只需一步,快速开始

扫一扫,访问微社区

查看: 149|回复: 1

[考试辅导] Oracle辅导:Oracle外键约束修改行为(六)

[复制链接]
发表于 2012-8-4 14:06:19 | 显示全部楼层 |阅读模式
Oracle的外键用来限制子表中参考的字段的值,必须在主表中存在。而且在主表的记录发生变化导致外键参考唯一约束值发生了变化时,定义了一系列的动作。
( e& T, d  @- F% G* H8 E( x3 P' V  前面几篇文章介绍了Oracle所支持的3种约束行为NO ACTION、DELETE SET NULL和DELETE CASCADE.至于SQL标准中定义的其他操作,Oracle只能通过触发器来实现,这里给出一个简单的SET DEFAULT操作的例子。0 R3 V" N% p* ]/ c8 ~
  SQL> DROP TABLE T_C;
) h1 P3 R( I; l0 y6 ]  表已删除。7 s& B+ _0 f3 M: v2 f4 E/ D
  SQL> DROP TABLE T_P;& I# \2 m; b" ]9 N6 X4 \3 G
  表已删除。  P4 T. c; C# y9 q, T
  SQL> CREATE TABLE T_P (ID NUMBER, NAME VARCHAR2(30));+ K! L/ W# K1 b
  表已创建。
7 C8 x9 e# ?4 q* x- n  SQL> ALTER TABLE T_P ADD PRIMARY KEY (ID);
1 [1 W/ I( U1 r3 G. w/ h  表已更改。7 O( _$ V* @# `( _2 e/ `
  SQL> CREATE TABLE T_C (ID NUMBER, FID NUMBER, NAME VARCHAR2(30));1 _" W2 a* @. S
  表已创建。$ _8 i. P: Z6 Z  r% P
  SQL> ALTER TABLE T_C ADD CONSTRAINT FK_T_C
6 \; a' K! [. O! c) {  2 FOREIGN KEY (FID)- Z- [7 J6 I8 K/ b
  3 REFERENCES T_P (ID);& K8 l, a* K/ r  w# \
  表已更改。
9 p9 _, a4 ^, Y$ E- G  SQL> INSERT INTO T_P VALUES (1, ’A’);
1 n2 t8 U. W7 E  已创建 1 行。4 q! X0 m" e: y/ U% s/ K
  SQL> INSERT INTO T_P VALUES (2, ’B’);0 S2 @& @2 R) q' _2 D; }8 v
  已创建 1 行。
. d' v$ q- j) I9 C  SQL> INSERT INTO T_C VALUES (1, 1, ’A’);
# I) u, }& s9 ?5 ?  已创建 1 行。
& I' w5 M2 K$ p; {  SQL> COMMIT;
, X# Y( n( U1 \0 r  提交完成。' q+ [! J/ O+ O7 _; e- l7 A
  检查一下直接更新操作:
3 L/ ]& ?" M$ {% [" c6 d# s6 X  SQL> SELECT * FROM T_P;
8 F; R9 l" u' ?# d+ C' V  ID NAME
( n; `6 {* a$ c0 r  ---------- ------------------------------0 I+ V; l, ?1 N8 ]
  1 A8 l) F" Q0 E1 X+ T
  2 B& h6 @$ w+ g7 N: e7 L
  SQL> SELECT * FROM T_C;8 V+ W# D) m/ L
  ID FID NAME1 }/ ?4 Q- T0 f' i# ]
  ---------- ---------- ------------------------------0 B" d8 f+ V( |0 k+ f1 [
  1 1 A
& g& E' \  K2 R( R0 r( E0 o2 F  SQL> UPDATE T_P SET ID = 3 WHERE ID = 1;
2 k  g# d" g9 k, n& l  UPDATE T_P SET ID = 3 WHERE ID = 1
回复

使用道具 举报

 楼主| 发表于 2012-8-4 14:06:20 | 显示全部楼层

Oracle辅导:Oracle外键约束修改行为(六)

  *第 1 行出现错误:
4 Y9 F- R' Q2 I. \" Z7 [( ?  ORA-02292: 违反完整约束条件 (YANGTK.FK_T_C) - 已找到子记录日志( s2 A! A. d# c* z* I) |$ ]
  下面构造一个触发器:
4 D( |, q  C) Q" g5 F  SQL> CREATE OR REPLACE TRIGGER T_P_SET_DEFAULT 9 |* H$ G( y: Q+ N$ y2 }/ y
  2 BEFORE DELETE OR UPDATE OF ID ON T_P5 t& v% R& H2 I; H
  3 FOR EACH ROW
1 L* A: j7 S( ~; k7 u3 ~  4 WHEN (NVL(NEW.ID, -1) != NVL(OLD.ID, -1))9 a9 d1 F. F4 E+ |* a$ a7 T7 _
  5 DECLARE! L& L' l! k9 M4 ]! Q
  6 V_STR VARCHAR2(32767);3 ~7 R2 L- K" b! O! ]
  7 BEGIN" n. ~! y5 h( O# Q  c8 i- N, u
  8 FOR I IN (SELECT OWNER, TABLE_NAME, CONSTRAINT_NAME $ o0 H' G5 X6 Q0 c& ^# ]4 U
  9 FROM ALL_CONSTRAINTS; R  D6 V6 \  U4 ?: r& @
  10 WHERE CONSTRAINT_TYPE = ’R’
: R0 G7 I# n& }$ A/ K" u  11 AND R_OWNER = ’YANGTK’; O& s  E- A- K6 a7 T1 D9 y
  12 AND R_CONSTRAINT_NAME IN # Q& M1 b# \( W5 k# c# U1 I7 a
  13 (SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME = ’T_P’))
. S. n+ W, e3 u2 @  14 LOOP
5 h# B) H% D* L1 }2 E0 B  15 V_STR := ’UPDATE ’ || I.TABLE_NAME || ’ SET ’; ; D( s( j. h1 j, s" Q5 o0 o2 ^, a1 o  B
  16 FOR J IN (SELECT A.COLUMN_NAME, B.DATA_DEFAULT- c9 J: B, t% _( W
  17 FROM ALL_CONS_COLUMNS A, ALL_TAB_COLUMNS B
0 R7 @5 ]- |" W1 k  18 WHERE A.OWNER = I.OWNER' ?7 w/ v" Q5 C/ u, D7 f
  19 AND B.OWNER = I.OWNER
% e7 o8 r  D5 |5 ]" J. O  20 AND A.TABLE_NAME = I.TABLE_NAME
3 [0 L, s4 ?, [2 l  21 AND B.TABLE_NAME = I.TABLE_NAME : b) c) v% C# h6 j1 [0 r0 l
  22 AND A.COLUMN_NAME = B.COLUMN_NAME' J- K/ K: f( b4 o: L
  23 AND CONSTRAINT_NAME = I.CONSTRAINT_NAME) ( O: P2 q* Y6 ?1 f" M' ^  M# H
  24 LOOP' ^: ]. P; m# H" c+ i6 c2 y$ ?
  25 V_STR := V_STR || J.COLUMN_NAME || ’ = ’ || J.DATA_DEFAULT ! c; H2 Y8 a0 i: j- E) d9 r* p
  26 || ’ WHERE ’ || J.COLUMN_NAME || ’ = ’ || :OLD.ID;
8 C- }% x# o2 s% U' E- B  27 END LOOP;9 o: p; i0 ~1 L" W
  28 EXECUTE IMMEDIATE V_STR;
1 Z6 d0 K/ y6 r0 W* Y$ B  29 END LOOP;
1 |! y2 U9 H, R, J! I/ ?  30 END;" E% J, G0 p- N$ \+ m$ w- O
  31 /
6 M9 F7 N2 s' {- h1 R( u  触发器已创建
- ?5 E5 Y% a' u0 V: ?  SQL> ALTER TABLE T_C MODIFY FID DEFAULT 2;
4 J2 l5 Q* k) ^' v* g7 f# X  表已更改。
4 `+ M, t( ^9 ?5 S% D  SQL> SELECT * FROM T_P;
( E8 n, v+ @& z0 ~/ m( M  D9 c) D  ID NAME
+ O6 u8 S, ~2 \) k9 y- Y7 q1 z8 u  ---------- ------------------------------
/ g( D: A9 q  P6 j& }7 i; w  1 A
3 x7 F+ _0 x3 P4 ^0 [  2 B' v0 V$ x. N! k4 t% h( w" z% ]
  SQL> SELECT * FROM T_C;( {- r$ b& i4 M7 T7 v
  ID FID NAME
% G2 S3 m' z9 W: I# _. T# w8 W  ---------- ---------- ------------------------------
$ B4 F; a5 t% b0 t6 |; r8 F* m2 `  1 1 A
3 G/ }) L1 U- o0 ~* O7 |  SQL> UPDATE T_P SET ID = 3 WHERE ID = 1;, M$ z* Q4 R. \
  已更新 1 行。% _' V$ {$ x; B& w6 J% {: t' e
  SQL> SELECT * FROM T_P;
% h; O" `; f4 u9 v& F& ^5 q  ID NAME
  Q- {: g* \4 y* `5 G4 A! G( g  ---------- ------------------------------
; U$ W0 i! o7 P( Z3 C  3 A1 |, g: U+ V2 ?, ^0 j% ^
  2 B/ y* ~& }* K- G, c
  SQL> SELECT * FROM T_C;9 O+ z5 ?+ ~( V3 l, P8 f
  ID FID NAME
  W! B% N# A! @6 U6 y9 Q  ---------- ---------- ------------------------------
3 D' q) _0 Q/ T2 x* V8 n& |' }. D  1 2 A7 I/ T( b9 l2 u5 o
  下面检查一下DELETE操作是否有效:5 P1 t  k5 b5 ?  l: v
  SQL> UPDATE T_C SET FID = 3;
( Y3 Q) b) A/ b& r2 f5 p- v3 b1 ^  已更新 1 行。" @4 m1 }" |! c- B. D
  SQL> SELECT * FROM T_C;
- p' X& ^4 W  r; }( w2 X( b  ID FID NAME
2 Y: z9 R/ }2 G/ x# T' P9 W  ---------- ---------- -----------------------------
' d9 B2 H- G. D. D: r2 F  1 3 A
- p8 o' G. W, c: z* n  SQL> DELETE T_P WHERE ID = 3;
& S- J7 ?$ M# s4 ^  已删除 1 行。
5 N' _3 U/ s" J( n9 \/ j  SQL> SELECT * FROM T_P;8 ~6 L3 q( n3 R( N! r8 y* W
  ID NAME
: {. R, _) ^* g/ f4 f( u- h" t8 X  ---------- ------------------------------
; m; N# u5 f5 h( c5 O  2 B
' W( |# o8 r# o2 _0 G% i  SQL> SELECT * FROM T_C;* c' x2 O+ B2 r5 E" @
  ID FID NAME
- p3 E  K; l+ |1 I- H5 m' w  B- X  ---------- ---------- -----------------------------$ i" z! b2 G! N* e
  1 2 A
回复 支持 反对

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

Archiver|手机版|小黑屋|Woexam.Com ( 湘ICP备18023104号 )

GMT+8, 2024-6-27 06:41 , Processed in 0.250013 second(s), 24 queries .

Powered by Discuz! X3.4 Licensed

© 2001-2017 Comsenz Inc.

快速回复 返回顶部 返回列表