Oracle中用表外键来保证系统参照完整性

黑侠

论坛元老

Rank: 7 Rank: 7 Rank: 7

社区建设勋章爱国勋章

发短消息
加为好友
当前离线

楼主大中小发表于 2008-1-24 08:18 只看该作者

Oracle中用表外键来保证系统参照完整性

Oracle中表的外键是保证系统参照完整性的手段，而参照完整性是指分布在两个表中的列所满足的具有主从性质的约束关系。外键涉及到两个表，其中一个称之为父项表，另一个称之为子项表。

父项表:（ parent table ）是参照约束的基础，即通过检查这张表的有效数据情况来判断约束是否成立，它是参照约束的条件，影响约束，而不受约束的任何影响。
; z$ x- G8 b; Z+ D/ z
我爱电脑技术论坛3 Y% I% e  \$ _/ C/ ?
子项表（ child table ）是参照约束的对象，当其发生变化，如有新数据输入时，通过比较父项表中的有效数据状况，来判断这些变化是否符合约束条件，若不符合，则拒绝要发生的变化。

电脑,技术,IT,学习,交流,网络安全,QQ,硬件,软件,编程,教程,建站1 M7 A. o( n) L
在实际应用系统中，开发者为了保证系统的完整性，一般要定义大量的外键。然而，如果外键的命名不规范，如采用系统自动生成的名称，则在以后的系统运行维护中会造成很大的麻烦。如在系统运行后，加载大量数据或者进行一些数据转换操作等时，出现外键错误时，根据系统提示的外键错误，根本不可能直接定位到那两个表间的外键发生错误，需要浪费很多时间查找造成错误发生的外键的父项表和子项表，然后才能进一步确定是那条记录违反了外键约束条件。通常，我们采用这样的命名规则来命名外键 FK_Child_table name_Parent_table name 。由于外键名称的最大长度限制在 30 个字符之内，对 child_table_name 和 Parent_table name 不一定和原表一模一样，可以采取一些简写的办法，但名称一定要能反映出约束的两个表。这里的命名， Child_table name 指子项表，也就是约束表， Parent_table name 是指父项表，也就是被约束的表。

打造最好的电脑自学交流论坛8 S1 t0 n: m$ R8 x6 E% ?5 L
下面来详细讨论如何将应用系统中不规范的外键命名修改为规范的外键名称。在讨论之前，需要提醒读者注意的是，完成下面的操作需要花费较长的时间，所以一定要规划在系统空闲时来完成。同时这里的外键更名，采用的方法是首先删除然后重建，涉及到删除应用系统对象的操作，所以在操作之前，为安全起见，应该备份应用系统。
我爱电脑技术社区--打造最好的电脑技术自学交流平台% S6 K+ M3 z/ Z; X3 U) q
电脑,技术,IT,学习,交流,网络安全,QQ,硬件,软件,编程,教程,建站, {% x, I  _" D, |
一、生成系统目前的外键报告单

首先生成系统目前模式下的所有外键情况报告单， SQL 脚本如下：
打造最好的电脑自学交流论坛6 s/ Y8 ]* A5 \4 f6 n  |! I

脚本1：列出当前模式下所有外键的报告表，可以将其 spool 到某个文件中
打造最好的电脑自学交流论坛# a; e2 b! i3 Z, V) F# h

SELECT RPAD(child.TABLE_NAME,25,' ') Child_Tablename,
电脑,技术,IT,学习,交流,网络安全,QQ,硬件,软件,编程,教程,建站  H7 V' p9 T5 O
RPAD(cp.COLUMN_NAME,17,' ') Referring_Column, 电脑,技术,IT,学习,交流,网络安全,QQ,硬件,软件,编程,教程,建站3 L+ q  C" [6 P* S
我爱电脑技术社区--打造最好的电脑技术自学交流平台0 K% O1 o/ Q+ F& k8 Y
RPAD(parent.TABLE_NAME,25,' ') Parent_Tablename,

RPAD(pc.COLUMN_NAME,15,' ') Referred_Column,
我爱电脑技术社区--打造最好的电脑技术自学交流平台1 E: z9 @6 n0 F6 |, C; H) U
RPAD(child.CONSTRAINT_NAME,25,' ') Constraint_Name 我爱电脑技术论坛# y! Q4 B* w# G) p

FROM USER_CONSTRAINTS child, 打造最好的电脑自学交流论坛( e$ A) k  h: w) n' a9 W* r

USER_CONSTRAINTS parent, 6 h8 \& v: Y' D

USER_CONS_COLUMNS cp,
www.520diannao.com- P4 Q5 n/ Q) ?$ s: P9 Y0 ~' o6 a
USER_CONS_COLUMNS pc 我爱电脑技术论坛/ y8 M- w0 m! G4 l

WHERE child.CONSTRAINT_TYPE = 'R' AND ) P2 {) r' f; s  o

child.R_CONSTRAINT_NAME = PARENT.CONSTRAINT_NAME AND 我爱电脑技术社区--打造最好的电脑技术自学交流平台/ [- N6 R: h+ @9 I0 @

child.CONSTRAINT_NAME = cp.CONSTRAINT_NAME AND

parent.CONSTRAINT_NAME = pc.CONSTRAINT_NAME AND 我爱电脑技术论坛  z, L/ X' q/ O9 X6 y% J
我爱电脑技术论坛2 b* g0 L# ]4 c4 \
cp.POSITION = pc.POSITION
我爱电脑技术论坛, i0 g; s. ?# i+ V! z' ?0 |
ORDER BY child.OWNER, 电脑,技术,IT,学习,交流,网络安全,QQ,硬件,软件,编程,教程,建站) K# G3 `& {5 ?
2 J3 Y% d; M  E7 Q
child.TABLE_NAME, 0 z$ Y' P  g: ^$ {
打造最好的电脑自学交流论坛6 n' M/ e: T$ q. ]0 y9 `$ j
child.CONSTRAINT_NAME, 我爱电脑技术论坛9 u$ ?! L" X9 O) u. w/ P
我爱电脑技术社区--打造最好的电脑技术自学交流平台1 B! K1 M* d" B+ G9 Z  B- s
cp.POSITION;
) v0 x8 _  s' N: Y
www.520diannao.com4 V; @, }% q& p" D/ ^

该脚本生成所在模式下的所有外键情况，包括外键名称，父项表名称，子项表名称以及引用的列名称等。在 SQL/PLUS 下运行该脚本，在运行该脚本之前，可以将输出 SPOOL 到本地某个文件中。同时要注意，如果应用系统中的外键比较多且复杂的话，这个脚本的运行时间会比较长。

2 r! t+ a* S  n) h
二、生成删除系统自动命名的外键脚本
电脑,技术,IT,学习,交流,网络安全,QQ,硬件,软件,编程,教程,建站# v% f+ F  l# B" S$ `. e
www.520diannao.com5 `% r. {' \4 E5 j
在 SQL/PLUS 下，运行下面的脚本来生成删除系统自动命名（也就是外键名称以 SYS 为前缀）的所有外键，和生成外键报告一样，将生成脚本 spool 到某个文件中。

; l2 P: ^6 D% K4 F
脚本2 ：删除系统自动生成的外键约束条件

SELECT 'ALTER TABLE ' || TABLE_NAME ||' '|| 打造最好的电脑自学交流论坛  f! K$ e- D7 D

'DROP CONSTRAINT ' || CONSTRAINT_NAME || ' ;'

FROM USER_CONSTRAINTS

WHERE CONSTRAINT_NAME LIKE 'SYS%' AND 我爱电脑技术社区--打造最好的电脑技术自学交流平台4 ?6 b9 N1 ?0 r) r. i" w2 d  x) @

CONSTRAINT_TYPE = 'R';www.520diannao.com' R9 m1 l9 e. U4 {9 _

我爱电脑技术论坛' A3 G/ E! r0 z3 X$ G
我爱电脑技术论坛. r* j" b2 D# u5 f, F) u

" l$ r1 j3 T' ]- s+ ~, T: M3 G

运行该脚本，系统生成如下所示的删除外键脚本：
www.520diannao.com  N, ]0 H( X4 m! a

ALTER TABLE DJ_NSRXX DROP CONSTRAINT SYS_C000231;我爱电脑技术论坛# m' `; N" A% o8 I$ M

我爱电脑技术社区--打造最好的电脑技术自学交流平台) I' @2 s4 R6 [( d/ j' k3 C5 I# t; J+ W

* z; W4 H5 ?0 E/ v
三、生成重新创建删除的外键脚本打造最好的电脑自学交流论坛6 u: ^2 p( [. f6 z
% q8 L( y; t9 M8 b  C6 s& Q$ A
我爱电脑技术社区--打造最好的电脑技术自学交流平台" M0 {. e& _5 r  t3 \
在 SQL/PLUS 下，运行下面的脚本来生成重新创建删除的外键脚本：我爱电脑技术社区--打造最好的电脑技术自学交流平台+ x) L# P# M# ]2 z. ~
我爱电脑技术社区--打造最好的电脑技术自学交流平台, m- V- {* E, Y
我爱电脑技术论坛; O- ^4 t. z0 J. {% S3 y
脚本 3 ：重新创建外键 8 \/ E/ }# s+ c' @/ k

我爱电脑技术社区--打造最好的电脑技术自学交流平台! k1 }4 G6 _* d' ^  k  K1 `
SELECT 'ALTER TABLE ' || child.TABLE_NAME||' ' || 电脑,技术,IT,学习,交流,网络安全,QQ,硬件,软件,编程,教程,建站* d( s8 X7 k- R( @$ K
www.520diannao.com) `1 a* ^  j4 }3 f% \; T6 H3 @% O
'ADD CONSTRAINT ' || ' 外键名称 ' ||' '|| ( U/ [  V) I3 ?: f. D
电脑,技术,IT,学习,交流,网络安全,QQ,硬件,软件,编程,教程,建站+ Y) r( u- v3 B+ `; Y. A# T
'FOREIGN KEY ' || '('|| cp.COLUMN_NAME || ')' || 打造最好的电脑自学交流论坛! J# m1 x# w' s% G8 i% r2 |
电脑,技术,IT,学习,交流,网络安全,QQ,硬件,软件,编程,教程,建站; u: {' K- {5 V+ R' ?1 Y: i
' '|| 'REFERENCES ' || parent.TABLE_NAME ||' '||

'('|| pc.COLUMN_NAME || ')'|| child.DELETE_RULE || ' ;'

FROM USER_CONSTRAINTS child,
我爱电脑技术论坛0 z0 i' j2 D: V+ p/ g# w
USER_CONSTRAINTS parent, 电脑,技术,IT,学习,交流,网络安全,QQ,硬件,软件,编程,教程,建站, h+ Q; w2 B+ X) A! Y/ s

USER_CONS_COLUMNS cp, 打造最好的电脑自学交流论坛" }* b1 C# m& j& Z
打造最好的电脑自学交流论坛' g+ ]" z9 A: g% ]- j/ ?6 \/ {
USER_CONS_COLUMNS pc

WHERE child.CONSTRAINT_TYPE = 'R' AND www.520diannao.com% e; [; [- A2 x, Z
打造最好的电脑自学交流论坛9 a) Q' m8 l( h$ l8 q& m4 A8 I
child.R_OWNER = PARENT.OWNER AND
1 x+ t) v. {. [2 j! I2 e. S' _
child.R_CONSTRAINT_NAME = PARENT.CONSTRAINT_NAME AND 电脑,技术,IT,学习,交流,网络安全,QQ,硬件,软件,编程,教程,建站' T4 k4 q# u+ b* }. y% k

child.CONSTRAINT_NAME = cp.CONSTRAINT_NAME AND www.520diannao.com  `& S- @9 q/ J
我爱电脑技术社区--打造最好的电脑技术自学交流平台% g- x; r/ y2 e) @7 y' `  {
parent.CONSTRAINT_NAME = pc.CONSTRAINT_NAME AND
- R& f. H8 r/ @! n5 H5 Y
cp.POSITION = pc.POSITION AND 我爱电脑技术社区--打造最好的电脑技术自学交流平台" u5 E$ H; ?0 m9 u# U

child.CONSTRAINT_NAME LIKE 'SYS%'
www.520diannao.com" a1 M$ H  A# M' ~
ORDER BY child.OWNER,

child.TABLE_NAME,
电脑,技术,IT,学习,交流,网络安全,QQ,硬件,软件,编程,教程,建站4 a% x( r( `* d9 T
child.CONSTRAINT_NAME, , A" G: w9 G0 @: m  Y+ f! p
4 B3 z4 q- c* ~1 [$ w+ X8 _
cp.POSITION;
  c) P! T% p& T  O1 s/ P
www.520diannao.com8 ]5 E  q6 E9 `+ V( @7 _4 |
" E7 n. k( Y( {. K& D) q
打造最好的电脑自学交流论坛  t* _) w8 X/ W) _9 {9 I2 E
我爱电脑技术社区--打造最好的电脑技术自学交流平台$ X$ M; j: N9 ~4 K# L

0 x8 Z1 z0 F) y- l/ V
运行该脚本，系统生成如下所示的创建外键脚本：我爱电脑技术论坛2 y; f* A( F' {& y: f; u( g4 U

ALTER TABLE DJ_NSRXX ADD CONSTRAINT 外键名称
FOREIGN KEY (RYDM) REFERENCES DM_GY_SWRY(RYDM);
我爱电脑技术社区--打造最好的电脑技术自学交流平台2 P% h& {6 H/ F. t7 P/ Q* T! }" O. R
* T5 f' t8 u5 b1 P8 _
我爱电脑技术论坛9 D7 ^+ o5 g+ E" K) R4 \
将上面的外键名称以上面介绍的规范命名规则命名的外键名称代替，就是:
打造最好的电脑自学交流论坛1 E3 b3 m% j! J; s- `# Z
) H0 ?& T) M: g% C. h4 S- L: u
ALTER TABLE DJ_NSRXX ADD CONSTRAINT FK_DJ_NSRXX_DM_GY_SWRY
FOREIGN KEY (RYDM) REFERENCES DM_GY_SWRY(RYDM);
9 i$ w) I- ?3 i6 K* Z5 i. t

我爱电脑技术社区--打造最好的电脑技术自学交流平台" H; a1 G  B  o# D3 J/ H
四、外键更名
我爱电脑技术社区--打造最好的电脑技术自学交流平台$ K! G* w5 P- D+ P
! R6 M/ J/ X! i  N) O
生成上面的两个脚本后，首先运行第二步中删除系统自动生成外键的脚本，将系统中命名不规范的外键删除，然后运行第三步中生成的创建外键的脚本，重新创建这些删除的外键，也就实现了对不规范外键的更名。

我爱电脑技术社区--打造最好的电脑技术自学交流平台; l3 _( c. \0 k1 ?5 Z
五、系统检查
我爱电脑技术社区--打造最好的电脑技术自学交流平台; }3 \3 _2 u: \- E4 `4 D: o, N

操作完成后，重新执行步骤1，再生成一个应用系统的外键报告单，作对比检查。如果正确无误，则更名成功

TOP

‹‹ 上一主题 | 下一主题 ››