Oracle 数据库的完整性约束机制

Oracle 数据库的完整性约束机制 Oracle利用完整性约束机制防止无效的数据进入数据库的基 表 关于同志近三年现实表现材料材料类招标技术评分表图表与交易pdf视力表打印pdf用图表说话 pdf ，如果任何DML执行结果破坏完整性约束，该语句被回滚并返回一上个错误。Oracle实现的完整性约束完全遵守ANSI X3.135-1989和ISO9075-1989标准。 利用完整性约束实施数据完整性规则有下列优点: ?定义或更改表时，不需要程序 设计 领导形象设计圆作业设计ao工艺污水处理厂设计附属工程施工组织设计清扫机器人结构设计 ，便很容易地编写程序并可消除程序性错误，其功能是由Oracle控制。所以说明性完整性约束优于应用代码和数据库触发器。 ?对表所定义的完整性约束是存储在数据字典中，所以由任何应用进入的数据都必须遵守与表相关联的完整性约束。 ?具有最大的开发能力。当由完整性约束所实施的事务规则改变时，管理员只需改变完整性约束的定义，所有应用自动地遵守所修改的约束。 ?由于完整性约束存储在数据字典中，数据库应用可利用这些信息，在SQL语句执行之前或由Oracle检查之前，就可立即反馈信息。 ?由于完整性约束说明的语义是清楚地定义，对于每一指定说明规则可实现性能优化。 ?由于完整性约束可临时地使不能，以致在装入大量数据时可避免约束检索的开销。当数据库装入完成时，完整性约束可容易地使其能，任何破坏完整性约束的任何新行在例外表中列出。 Oracle的DBA和应用开始者对列的值输入可使用的完整性约束有下列类型: ?NOT NULL约束:如果在表的一列的值不允许为空，则需在该列指定NOT NULL约束。 ?UNIQUE码约束:在表指定的列或组列上不允许两行是具有重复值时，则需要该列或组列上指定UNIQUE码完整性约束。在UNIQUE码约束定义中的列或组列称为唯一码。所有唯一完整性约束是用索引方法实施。 ?PRIMARY KEY约束:在数据库中每一个表可有一个PRIMARY KEY约束。包含在PRIMARY KEY完整性约束的列或组列称为主码，每个表可有一个主码。Oracle使用索引实施PRIMARY KEY约束。 ?FOREIGN KEY约束(可称引用约束):在关系数据库中表可通过公共列相关联，该 规则控制必须维护的列之间的关系。包含在引用完整性约束定义的列或组列称为外来码。由外来码所引用的表中的唯一码或方码，称为引用码。包含有外来码的表称为子表或从属表。由子表的外来码所引用的表称为双亲表或引用表。如果对表的每一行，其外来码的值必须与主码中一值相匹配，则需指定引用完整性约束。 ?CHECK约束:表的每行对一指定的条件必须是TRUE或未知，则需在一列或列组上指定CHECK完整性约束。如果在发出一个DML语句时，CHECK约束的条件计算得FALSE时，该语句被回滚。 一、数据完整性的类型 关系模型的三类完整性是实体完整性、参照完整性和用户完整性。 实体完整性和参照完整性是关系模型必须满足的完整性约束条件，应由关系系统自动支持。 1、实体完整性 (entity integrity) 实体完整性是基于主码的，一个主码由一个或多个属性组成。实体完整性要求主码中的任一属性(列)不能为空，所谓空值 是“不知道”或“无意义”的值。之所以要保证实体完整性主要是因为:在关系中，每一个元组的区分是依据主码值的不同，若主码值取空值，则不能标明该元组的 存在。 2、参照完整性 (referential integrity) 参照完整性是基于外码的，若基本关系R中含有与另一基本关系S的主码PK相对应的属性组FK(FK称为R的外码),则参照完整性要求,对R中的每个元组在FK上的值必须是S中某个元组的PK值,或者为空值。 参照完整性的合理性在于:R中的外码只能对S中主码的引用,不能是S中主码没有的值.如学生和选课表两关系,选课表中的学号是外码,它是学生表的主键,若选课表中出现了某个学生表中没有的学号,即某个学生还没有注册,却已有了选课记录,这显然是不合理的。 3、用户定义的完整性(user-defined integrity) 实体完整性和参照完整性适用于任何关系数据库系统。除此之外，不同的关系数据库系统根据其应用环境的不同，往往还需 要一些特殊的约束条件。用户定义的完整性就是针对 某一具体关系数据库的约束条件，它反映某一具体应用所涉及的数据必须满足的语义要求。如:学生的成绩一般 情况下的取值范围在0-100之间。 二、Oracle的数据完整性的实现 1、Oracle中的实体完整性 实体完整性规则要求主属性非空。Oracle在CREATE TABLE语句中提供了PRIMARY KEY子句，供用户在建表时指定关系的主码列。例如:在学生选课数据库中，要定义Student表的sno属性为主码，可使用如下语句: SQL>CREATE TABLE Student ( sno NUMBER(8), sname VARCHAR(20), sage NUMBER(20),CONSTRAINT PK_SNO PRIMARY KEY (sno)); 其中:PRIMARY KEY(SNO)表示SNO是Student表的主码。PK_SNO是此主码约束名。 在用PRIMARY KEY语句定义了关系的主码后，每当用户程序对主码列进行更新操作时，系统自动进行完整性检查，凡操作使主码值为空值或使主码值在表中不唯一，系统拒绝此操作，从而保证了实体完整性。 2、ORACLE中的参照完整性 Oracle的CREATE TABLE语句不仅可以定义关系的实体完整性规则，也可以定义参照完整性规则，即用户可以在建表时用FOREIGN KEY 子句定义哪些列为外码列，用REFERENCES子句指明这些外码相应于哪个表的主码，用ON DELETE CASCADE 子句指明在删除被参照关系的元组时，同时删除参照关系中外码值等于被删除的被参照关系的元组中主码值中的元组。 SQL>CREATE TABLE SC (sno NUMBER(8); cno NUMBER(2), grade NUMBER(3), CONSTRAINT PK_SC PRIMARY KEY(sno,cno),CONSTRAINT FK_SNO FOREIGN KEY (sno) REFERENCES student(sno) on delete CASCADE); 3、ORACLE中的用户自定义完整性 除实体完整性和参照完整性外、应用系统中往往还需要定义与应用有关的完整性约束。 例如:要求某一列的值能取空值;某一列的值在表中是唯一的;某一列的值要在某个范围中等。ORACLE允许用户在建表时定义下列完整性约束。 A、例如:部门表中保证部门名称唯一 SQL>CREATE TABLE DEPT (deptno NUMBER, dname VARCHAR(9) CONSTRAINT U1 unique, loc VARCHAR(10), CONSTRAINT PK_DEPT PRIMARY KEY (deptno)); B、例如:学生成绩大于0小于等于100(检查列值是否满足一个布尔表达式)。 SQL>CREATE TABLE SC (sno NUMBER(8); cno NUMBER(2), grade NUMBER(3) CONSTRAINT C1 CHECK (0 CREATE TABLEtab_01(operated date); 表被创建 SQL> INSERT INTO tab_01VALUES('2010-01-01'); INSERT INTO tab_01 VALUES('2010-01-01') ORA-01861: 文字与格式字符串不匹配 2 域 数据类型下所允许的最大数值。例如常用的varchar2(20),number(10,2)等。对于变长字 符串类型varchar,varchar2,必须定义长度，char的默认长度1。 SQL> alter table tab_01add name varchar2; alter table tab_01 add namevarchar2 ORA-00906: 缺失左括号 SQL> alter table tab_01 addname char; 表被改变 SQL> desc tab_01; Name Type Nullable Default Comments -------- ------- --------------- -------- OPERATED DATE Y NAME CHAR(1) Y 对于数值类型number，oracle根据os情况设定默认域范围，一般能够存储38位整数， number(m,n)表示允许保存m-n位整数和n位小数。其他数据类型，例如date类型精确到 s,datestamp精确到0.03s，随着oracle版本的升级不断地会有一些更加精确的数据类型加入 进来。 语法如下: createdomain domain_name as data type [default default_value] [constraint constraint_name] check(value conditionexpression) 例如: createdomain valid_no as int constraint constraint_no check(value between 100 and 999) 然后创建表时，使用valid_no域。 createtable TestDomain ( emp_id valid_no, emp_name varchar(10), ) 3 Not null SQL> select unique constraint_type from user_constraints; CONSTRAINT_TYPE --------------- C O P R U (同用户自定属性非空是数据库的其中一种约束，在数据字典视图中类型编号为”C”义视图类型相同)它描述的是业务 规范 编程规范下载gsp规范下载钢格栅规范下载警徽规范下载建设厅规范下载 中实体在某方面的特性在记录中不能为空，例如:身份证上面的身份证编号和姓名等属性。确定一个属性在业务规范中非空可以确保记录中一定存在数据，能够给后期前台程序的查询和统计等开发节省很多的判断精力，例如selectcount(*) into … from … where … 如果满足条件的记录不存但是又没有exception处理则程序就会出现错误，影响进度。 在工作中发现我们建立表的时候一般对于not null并不关心一个规范的约束名称(大家常用 的pl/sql工具并不提供对not null的约束名称，只是在非空上打挑而已)定义约束名称的时候不太注意constraint_name的规范，这样不好，当现场环境比较恶劣，光秃秃没有pl/sql工具只有sql*plus可供使用的时候(我在丹东港做生产的时候就是)，我们需要明确区别各属性约束情况的时候，只按照系统自动提供的以sys+编号的约束名是很费力气的，影响操作效率。 4 Unique 唯一索引是保证该数据项在一个记录集的属性集合中唯一出现的约束，在数据字典视图中类型编号为”U”，系统在定义pk的时候会自动生成一条同名的唯一索引(和主键应该是一个)。主键和唯一索引在应用方便的区别有两点: 1) 主键属性不能为空，但是唯一索引允许为空; 2) 当定义外部键约束的时候，作为被参照关系的表(table)或者实体视图(materialized view) 必须存在主键，唯一索引建立不了。 第一点很重要，可能是工作习惯的问题吧，在实际的工作中我发现有的同时喜欢使用unique index来代替primary key，当然了，还有一个附带的属性not null，unique index + not null 可以用来代替pk的功能，但是两则的概念还是不同的。另外由于很少在数据库结构的模式对象中使用foreign key所以没有暴露出无法定义的问题，但是这是一个隐患。 5 Foreign key 为了保证数据的完整性需要在不同的实体之间的数据保持一种关系—一个数据项必须存在于另外一个数据项的基础之上，满足所谓的”皮之不存毛将焉附”，在数据字典视图中类型编号为”F”。外部键作为参照完整性的体现是有一系列的数据理论支持的: 设F是基本关系R的一个属性集合(对应n个属性)，但是F不是R的码，如果F和关系S的主码Sk相对应，则称F是基本关系R的外码，称R为参照关系，称S为被参照关系或者目标关系。参照关系R的外码F必须和被参照关系S的主码Ks在同一个域中，如果属性集合F为基本关系R的外码，与被参照关系得主码Sk向对应(可以是同一个关系)，则对于R中每个元组在F上的数据项必须为 l 空值(如果允许为空) l S中某个元组的主码值 Foreign key其实是一把双刃剑:使用的科学能够很好地保证业务逻辑的完整性，给前台开发工作提供很大的便利，许多判断条件就不用再考虑;使用的不科学会给我们的开发工 作或者是在数据库的直接处理方面造成:删不了也插不进的尴尬。科学使用fk是在系统设计阶段就开始考虑的工作，这和对业务的精通、对数据库相关功能的理解和经验是分不开的，我的理解是要多做尝试，操练炖熟，最后在实践中广泛应用，能给前台开发节约很大的成本。 删除主表之前先要删除外键约束，可以使用cascade constraints限定词;对主表进行truncate操作之前，先要删除外键约束;删除主表所在表空间之前先要删除外键约束; 在使用外键约束的主表进行更新操作，要锁定从表，如果从表对应主表外键的列上建有索引，则只需锁定索引. 6 Primary key 首先需要定义实体:客观存在又能够区分的事物。实体可以使具体存在的实物，例如:桌子、椅子等，也可以是一种联系:例如老师和学生组成的师生关系。实体、属性、联系组成了ER模型的三要素。无疑能够唯一区别实体集中每个实体唯一性的集合我们称为主键。主键是主码的一部分，例如:一个班级中约定除了学生编号不允许重复外学生的姓名也不允许重复，或者是学生的生日也不允许重复，在这种前提下，要唯一确定一个学生就存在三种选择:学号、姓名、生日，这些能够唯一区分元组的属性集合我们称为主码，在主码中选择一个作为主键需要考虑一些因素:例如:单独的学号可以做主键，单独的姓名也能作主键，学号+姓名也能做主键，到底启用哪个呢,主键要满足 l 集合的最小化 l 系统的可扩展 l 建议采用不能描述实体某一方面特性的数据项 一个班级的学生姓名一般不会重复，但是一个年级一个学校呢,一个班级的学生生日一般不会重复，但是一个年级一个学校呢,采用由一定规则生成的学生编号就可以解决这个问题，所以建议使用学号作为主键。 7 Check Check 约束 在数据列上Check 约束需要 一个特殊的布尔条件或者将数据列设置成TRUE，至少一个数据列的值是NULL，Check约束用于增强表中数据内容的简单的商业规则。用户使用Check约束保证数据规则的一致性。Check约束可以涉及该行同属Check约束的其他数据列但不能涉及其他行或其他表，或调用函数SYSDATE,UID,USER,USERENV。如果用户的商业规则需要这类的数据检查，那么可以使用触发器。Check约束不保护LOB数据类型的数据列和对象、嵌套表、VARRY、ref等。单一数据列可以有多个Check约束保护，一个Check约束可以保护多个数据列。 创建表的Check约束使用CREATE TABLE语句，更改表的约束使用ALTER TABLE语句。 语法: CONSTRAINT [constraint_name] CHECK (condition); Check约束可以被创建或增加为一个表约束，当Check约束保护多个数据列时，必须使用表约束语法。约束名是可选的并且如果这个名字不存在，那么oracle将产生一个以SYS_开始的唯一的名字。 例: CREATE TABLE policies (policy_id NUMBER, holder_name VARCHAR2(40), gender VARCHAR2(1) constraint chk_gender CHECK (gender in ('M','F'), marital_status VARCHAR2(1), date_of_birth DATE, constraint chk_marital CHECK (marital_status in('S','M','D','W')) ); 8 View with read only SQL> create or replace view v_test as 2 Select "C1","C2","ITEM","BIN","ID","TIME" 3 from test 4 Where item = 'a006' 5 With Read Only 6 ; 视图被创建 因为某种业务逻辑我定义了一个单表视图，概括地说单表视图允许在前台进行数据修改，为了降低对数据dml操作的隐含风险来更佳稳妥的保存我的数据，我希望使用这个视图的人员或者是我grant select on v_test to 不能修改视图定义对应得的数据。例如:我只希望用户能够看到视图定义中item部门为'a006'的信息，但是用户不能dml，为了满足这种需求，oracle提供了在数据字典视图中约束类型为’O’的约束: select unique constraint_type 约束类型 from user_constraints 约束类型 -------- C O P R U 它可以保证由视图的只读功能。 9 View with check Option SQL> create or replace view v_test as 2 Select "C1","C2","ITEM","BIN","ID","TIME" 3 from test 4 Where item = 'a006' 5 With Check Option 6 ; 视图被创建 因为某种业务逻辑我定义了一个单表视图，概括地说单表视图允许在前台进行数据修改，但是我希望前台插入或者修改的数据是满足我视图中条件的才行，例如:视图定义中item部门为'a006'，如果现在程序插入一个人，部门选择了’a007’就是我所不允许的，这样前台程序就要加以屏蔽掉部门的下拉选择列表，但是这给用户的操作界面可能就不够友好了。为了处理这种需求，oracle提供了在数据字典视图中约束类型为’V’的约束，它可以保证由视图入口dml的属性都需要满足where clause指定的条件，否则会提示错误，例如: 10 断言约束:不必与特定的列绑定，可以理解为能应用于多个表的check约束，因此必须在表定义之外独立创建断言。 语法如下: create assertion constraint_name check search condition 例如: create assertion name check (Emp_Sal.emp_id in(select emp_id from EmployeeInfo where emp_name is not null) 添加断言后，每当试图添加或修改Emp_Sal表中的数据时，就对断言中的搜索条件求值，如果为false，则取消执行，给出提示。 11 约束的状态 约束存在两种状态:disable--声明约束是否可用;validat--是否执行 disable novalidate: 可以修改和新增违反完整性约束的数据; disable validate: 不允许修改被约束限定的列，此外在约束列上建立的索引也会被删除; enable novalidate: 新增数据必须符合约束条件，不检查以往数据; enable validate: 新增数据必须符合约束条件，并检查以往数据。因此约束从disable到enable的状态变迁，会导致整张表被锁定，用来检查以往数据的完整性。 声明约束的状态能够给我们解决很大的问题 12 倒入初始数据 程序初期我们可能需要把以前的历史数据整理以后按照excel表的方式使用oracle第三方工具向新模式对象中插入记录，因为需求设计的时候已经声明约束，有部分数据可能不满足条件或者考虑到性能因素在前期批量insert的时候不需要启用约束，就需要改变约束的状态来控制这个过程。 在中间过程中引入声明约束 做丹东港生产的后期遇到一个问题:用户发现需要对某两个关系做fk,但是以前的历史数据不想反推给修改，也修改不过来，只需要对以后的数据保证参照完整就行，操作如下: alter table t1 add constraint fk_t1_col1 foreign key (col1) references t2 (primary key column) enable novalidate; 一条语句在sql*plus环境下执行就很好的解决了这个问题，前台程序不需要作任何的调整，不影响现场生产。 13 检查约束 设置立即检查immediate的约束在sql语句执行时检查，检查不通过，只会滚当前语句的操作; 设置延迟检查defered的约束在事务提交后检查，检查不通过回滚整个事务; 使用set constraints defered/immediate来设置约束检查，使用alter session语句也可以修改会话内的约束检查—无法直接修改设定constraint的deferrable和immediate约束检查状态，只有drop然后create来重新建立约束状态(缺省值immediate) alter table DEPT drop constraint CD; alter table DEPT add constraint CD check (deptno <100) deferrable; 14 定义约束的考虑 将约束的索引和表放在不同的表空间; 保存大块数据时，先disable约束再enable约束会获得较好性能，适用非唯一索引也是个比较好的方法; 存在自参照的约束时，使用延迟检查。 15 两个数据字典的视图 Oracle提供两个数据字典视图来记录声明约束: 和dba_cons_columns 和user_cons_columns 通过这两个数据字典视图可以打开通向约束的大门，给我们提供源源不断的后台模式 对象的信息。 16 lock锁 为了保证数据的完整性引入了锁的概念。 1) dml 2) Ddl 排他ddl 共享ddl 可中断解析 3) latch 4) 手动锁定 Select … for update [nowait|wait[n]] --多表关联时锁定指定表的数据行 5) 和用户定义锁 使用dbms_lock建立自己的锁 6) Select … for update [nowait|wait[n]] skip locked 7) Set transaction read only 8) Set transaction isolation level{serializable|read committed}