Oracle数据库的完整性约束和序列规则详解Word格式.docx
《Oracle数据库的完整性约束和序列规则详解Word格式.docx》由会员分享，可在线阅读，更多相关《Oracle数据库的完整性约束和序列规则详解Word格式.docx（11页珍藏版）》请在冰点文库上搜索。
单一数据列可以有多个Check约束保护，一个Check约束可以保护多个数据列。
创建表的Check约束使用CREATETABLE语句，更改表的约束使用ALTERTABLE语句。
语法：
CONSTRAINT[constraint_name]CHECK（condition）;
Check约束可以被创建或增加为一个表约束，当Check约束保护多个数据列时，必须使用表约束语法。
约束名是可选的并且如果这个名字不存在，那么oracle将产生一个以SYS_开始的唯一的名字。
例：
CREATETABLEpolicies
（policy_idNUMBER,
holder_nameVARCHAR2（40）,
genderVARCHAR2
（1）constraintchk_genderCHECK（genderin（'
M'
'
F'
）,
marital_statusVARCHAR2
（1）,
date_of_birthDATE,
constraintchk_maritalCHECK（marital_statusin（'
S'
D'
W'
））
）;
NOTNULL约束
NOTNULL约束应用在单一的数据列上，并且他保护的数据列必须要有数据值。
缺省状况下，ORACLE允许任何列都可以有NULL值。
某些商业规则要求某数据列必须要有值，NOTNULL约束将确保该列的所有数据行都有值。
holder_nameVARCHAR2（40）NOTNULL,
genderVARCHAR2
（1）,
date_of_birthDATENOTNULL
对于NOTNULL的ALTERTABLE语句与其他约束稍微有点不同。
ALTERTABLEpoliciesMODIFYholder_nameNOTNULL
唯一性约束（Uniqueconstraint）
唯一性约束可以保护表中多个数据列，保证在保护的数据列中任何两行的数据都不相同。
唯一性约束与表一起创建，在唯一性约束创建后，可以使用ALTERTABLE语句修改。
column_namedata_typeCONSTRAINTconstraint_nameUNIQUE
如果唯一性约束保护多个数据列，那么唯一性约束要作为表约束增加。
语法如下：
CONSTRAINTconstraint_name（column）
UNIQUEUSINGINDEXTABLESPACE
（tablespace_name）STORAGE（storedclause）
唯一性约束由一个B-tree索引增强，所以可以在USING子串中为索引使用特殊特征，比如表空间或存储参数。
CREATETABLE语句在创建唯一性约束的同时也给目标数据列建立了一个唯一的索引。
CREATETABLEinsured_autos
（policy_idNUMBERCONSTRAINTpk_policiesPRIMARYKEY,
vinVARCHAR2（10）,
coverage_beginDATE,
coverage_termNUMBER,
CONSTRAINunique_autoUNIQUE（policy_id,vin）USING
INDEXTABLESPACEindexSTORAGE（INITIAL1MNEXT10MPCTINCREASE0）
用户可以禁用未以性约束，但他仍然存在，禁用唯一性约束使用ALTERTABLE语句。
ALTERTABLEinsured_autosDISABLECONSTRAINunique_name;
删除唯一性约束，使用ALTERTABLE....DROPCONSTRAIN语句：
ALTERTABLEinsured_autosDROPCONSTRAINunique_name;
注意用户不能删除在有外部键指向的表的唯一性约束。
这种情况下用户必须首先禁用或删除外部键（foreignkey）。
删除或禁用唯一性约束通常同时删除相关联的唯一索引，因而降低了数据库性能。
经常删除或禁用唯一性约束有可能导致丢失索引带来的性能错误。
要避免这样错误，可以采取下面的步骤：
1、在唯一性约束保护的数据列上创建非唯一性索引。
2、添加唯一性约束。
主键（PrimaryKey）约束
表有唯一的主键约束。
表的主键可以保护一个或多个列，主键约束可与NOTNULL约束共同作用于每一数据列。
NOTNULL约束和唯一性约束的组合将保证主键唯一地标识每一行。
像唯一性约束一样，主键由B-tree索引增强。
创建主键约束使用CREATETABLE语句与表一起创建，如果表已经创建了，可以使用ALTERTABLE语句。
date_of_birthDATE
与唯一性约束一样，如果主键约束保护多个数据列，那么必须作为一个表约束创建。
vinVARCHAR2（40）,
CONSTRAINTpk_insured_autosPRIMARYKEY（policy_id,vin）
USINGINDEXTABLESPACEindex
STORAGE（INITIAL1MNEXT10MPCTINCREASE0）
禁用或删除主键必须与ALTERTABLE语句一起使用:
ALTERTABLEpoliciesDROPPRIMARYKEY;
ALTERTABLEpoliciesDISABLEPRIMARYKEY;
外部键约束（Foreignkeyconstraint）
外部键约束保护一个或多个数据列，保证每个数据行的数据包含一个或多个null值，或者在保护的数据列上同时拥有主键约束或唯一性约束。
引用（主键或唯一性约束）约束可以保护同一个表，也可以保护不同的表。
与主键和唯一性约束不同外部键不会隐式建立一个B-tree索引。
在处理外部键时，我们常常使用术语父表（parenttable）和子表（childtable）,父表表示被引用主键或唯一性约束的表，子表表示引用主键和唯一性约束的表。
创建外部键使用CREATETABLE语句，如果表已经建立了，那么使用ALTERTABLE语句。
（policy_idNUMBERCONSTRAINTpolicy_fk
REFERENCEpolicies（policy_id
ONDELETECASCADE,
makeVARCHAR2（30）,
modelVARCHAR（30）,
yearNUMBER,
CONSTRAINauto_fkFROEIGNKEY（make,model,year）
REFERENCESautomobiles（make,model,year）
ONDELETESETNULL
ONDELETE子串告诉ORACLE如果父纪录（parentrecord）被删除后，子记录做什么。
缺省情况下禁止在子记录还存在的情况下删除父纪录。
外部键和NULL值
在外部键约束保护的数据列中NULL值的处理可能产生不可预料的结果。
ORACLE使用ISOstandarMatchNone规则增强外部键约束。
这个规则规定如果任何外部键作用的数据列包含有一个NULL值，那么任何保留该键的数据列在父表中没有匹配值。
比如，在父表AUTOMOBILES中，主键作用于数据列MAKE，MODEL，YEAR上，用户使用的表INSURED_AUTOS有一个外部约束指向AOTOMOBILES，注意在INSURES_AUTOS中有一数据行的MODEL列为NULL值，这一行数据已经通过约束检查，即使MAKE列也没有显示在父表AUTOMOBILES中，如下表：
表1AUTOMOBILES
表2INSURED_AUTOS
延迟约束检验（DeferredConstraintChecking）
约束检验分两种情况，一种是在每一条语句结束后检验数据是否满足约束条件，这种检验称为立即约束检验（immediatelychecking）,另一种是在事务处理完成之后对数据进行检验称之为延迟约束检验。
在缺省情况下Oracle约束检验是立即检验（immediatelychecking）,如果不满足约束将先是一条错误信息，但用户可以通过SETCONSTRAINT语句选择延迟约束检验。
SETCONSTRAINTconstraint_name|ALLDEFEERRED|IMMEDIATE--;
序列（Sequences）
Oracle序列是一个连续的数字生成器。
序列常用于人为的关键字，或给数据行排序否则数据行是无序的。
像约束一样，序列只存在于数据字典中。
序列号可以被设置为上升、下降，可以没有限制或重复使用直到一个限制值。
创建序列使用SETSEQUENCE语句。
CREATESEQUENCE[schema]sequenceKEYWORD
KEYWORD包括下面的值：
删除序列使用DROPSEQUENCE语句。
DROPSEQUENCEsequence_name
索引（INDEXES）
索引是一种可以提高查询性能的数据结构，在这一部分我们将讨论索引如何提高查询性能的。
ORACLE提供了以下几种索引：
B-Tree、哈希（hash）、位图（bitmap）等索引类型；
基于原始表的索引；
基于函数的索引；
域（Domain）索引。
实际应用中主要是B-Tree索引和位图索引，所以我们将集中讨论这两种索引类型。
B-Tree索引
B-Tree索引是最普通的索引，缺省条件下建立的索引就是这种类型的索引。
B-Tree索引可以是唯一或非唯一的，可以是单一的（基于一列）或连接的（多列）。
B-Tree索引在检索高基数数据列（高基数数据列是指该列有很多不同的值）时提供了最好的性能。
对于取出较小的数据B-Tree索引比全表检索提供了更有效的方法。
但当检查的范围超过表的10%时就不能提高取回数据的性能。
正如名字所暗示的那样，B-Tree索引是基于二元树的，由枝干块（branchblock）和树叶块（leafblock）组成,枝干块包含了索引列（关键字）和另一索引的地址。
树叶块包含了关键字和给表中每个匹配行的ROWID。
位图索引
位图索引主要用于决策支持系统或静态数据，不支持行级锁定。
位图索引可以是简单的（单列）也可以是连接的（多列），但在实践中绝大多数是简单的。
位图索引最好用于低到中群集（cardinality）列，在这些列上多位图索引可以与AND或OR操作符结合使用。
位图索引使用位图作为键值，对于表中的每一数据行位图包含了TRUE
（1）、FALSE（0）、或NULL值。
位图索引的位图存放在B-Tree结构的页节点中。
B-Tree结构使查找位图非常方便和快速。
另外，位图以一种压缩格式存放，因此占用的磁盘空间比B-Tree索引要小得多。
Oracle创建自增字段方法-ORACLESEQUENCE的简单介绍
先假设有这么一个表：
create
table
S_Depart
（
DepartId
INT
not
null,
DepartName
NVARCHAR2（40）
DepartOrder
default
0,
constraint
PK_S_DEPART
primary
key
（DepartId）
在oracle中sequence就是所谓的序列号，每次取的时候它会自动增加，一般用在需要按序列号排序的地方。
1、CreateSequence
你首先要有CREATESEQUENCE或者CREATEANYSEQUENCE权限，
CREATESEQUENCEemp_sequence
INCREMENTBY1--每次加几个
STARTWITH1--从1开始计数
NOMAXvalue--不设置最大值
NOCYCLE--一直累加，不循环
CACHE10;
--设置缓存cache个序列，如果系统down掉了或者其它情况将会导致序列不连续，也可以设置为---------NOCACHE
针对S_Depart创建的sequence如下：
sequence
S_S_DEPART
minvalue
1
maxvalue
999999999999999999999999999
start
with
increment
by
nocache;
一旦定义了emp_sequence，你就可以用CURRVAL，NEXTVAL
CURRVAL=返回sequence的当前值
NEXTVAL=增加sequence的值，然后返回sequence值
比如：
emp_sequence.CURRVAL
emp_sequence.NEXTVAL
可以使用sequence的地方：
-不包含子查询、snapshot、VIEW的SELECT语句
-INSERT语句的子查询中
-NSERT语句的valueS中
-UPDATE的SET中
可以看如下例子：
insert
into
S_Depart（departId,Departname,Departorder）values（S_S_Depart.Nextval,'
12345'
1）;
SELECTempseq.currvalFROMDUAL;
但是要注意的是：
-第一次NEXTVAL返回的是初始值；
随后的NEXTVAL会自动增加你定义的INCREMENTBY值，然后返回增加后的值。
CURRVAL总是返回当前SEQUENCE的值，但是在第一次NEXTVAL初始化之后才能使用CURRVAL，否则会出错。
一次NEXTVAL会增加一次SEQUENCE的值，所以如果你在同一个语句里面使用多个NEXTVAL，其值就是不一样的。
明白？
-如果指定CACHE值，ORACLE就可以预先在内存里面放置一些sequence，这样存取的快些。
cache里面的取完后，oracle自动再取一组到cache。
使用cache或许会跳号，比如数据库突然不正常down掉（shutdownabort）,cache中的sequence就会丢失.所以可以在createsequence的时候用nocache防止这种情况。
2、AlterSequence
你或者是该sequence的owner，或者有ALTERANYSEQUENCE权限才能改动sequence.可以alter除start至以外的所有sequence参数.如果想要改变start值，必须dropsequence再re-create.
Altersequence的例子
ALTERSEQUENCEemp_sequence
INCREMENTBY10
MAXvalue10000
CYCLE--到10000后从头开始
NOCACHE;
影响Sequence的初始化参数：
SEQUENCE_CACHE_ENTRIES=设置能同时被cache的sequence数目。
可以很简单的DropSequence
DROPSEQUENCEorder_seq;
一个简单的例子：
createsequenceSEQ_ID
minvalue1
maxvalue99999999
startwith1
incrementby1
nocache
order;
建解发器代码为：
createorreplacetriggertri_test_id
beforeinserton
--S_Depart
是表名
foreachrow
declare
nextidnumber;
begin
IF:
new.DepartIdISNULLor:
new.DepartId=0THEN
--DepartId是列名
selectSEQ_ID.nextval--SEQ_ID正是刚才创建的
intonextid
fromsys.dual;
:
new.DepartId:
=nextid;
endif;
endtri_test_id;
OK，上面的代码就可以实现自动递增的功能了。
升级会员 