管理Oracle约束与分区表Oracle认证考试

文章作者 100test 发表时间 2009:07:08 04:28:32
来源 100Test.Com百考试题网

　　1．约束
　　作用：
　　约束用于确保数据库数据的完整性,在oracle数据库中,可以使用约束,触发器和应用代码(过程,函数)3种方法实现数据完整性,这3种方法中,因为约束易于维护,并且具有最好的性能,所以实现数据完整性首选约束.
　　分类：
　　约束的种类有：not null,unique,primary key,foreign key,check
　　Not null确保字段值不能为空
　　Unique:确保字段值唯一性
　　Primary key,最常用的约束（主键约束），主键约束的列值不仅不能重复，也不能为NULL，注意一张表最多只能有一个主键约束，当定义主键约束后oracle自动建立一个以主键为关键字段的索引。
　　Foreign key:定义了主从表之间的关系，foreign要定义在从表上，但主表必须具有主键约束或唯一约束，当定义froeign key后外部键列的数据必须在主表的主键列中存在，或者为NULL
　　Check::用于强制表行数据必须满足的条件，如工资表，工人工资必须在2000-5000之间
　　约束状态
　　enable validate:是默认,新旧数据同时满足约束规则
　　enable novalidate:旧数据可以不满足,检新插入的数据要满足约束
　　disable validate:不允许在表上执行任何DML操作,主要用在分区表,对于主键和唯一约事,会删除相应的唯一索引,但约束状态任可用
　　disable novalidate数据可不满足约束规则,对于主键和唯一约事,会删除相应的唯一索引,
　　约束常用语句
　　create table t(i number,v mubmer not null)
　　create table t(i number,v mubmer unique)
　　create table t(i number constraint pk_i primary key,v number)
　　create table t2(c number,d number,constraint fk_d foreign key(c),references t1(v)).
　　alter table t add constraint pk_i primary key (i)
　　alter table t modify i not null.
　　alter table t add constraint t_i unique(i)[(create index ind_name on t(i))].
　　alter table t add constraint t_i check(i in (1,2,3,4,5)).
　　alter table t disable novalidate constraint i
　　alter table t enable novalidate constraint check_i
　　alter table t 0drop constraint i.
　　alter table t 0drop primary key i.
　　#常用的数据字典
　　dba_constraints
　　dba_cons_columns
　　user_cons_columns
　　user_constraints
　　简单应用
　　检验当为一个表建立主键索时后，这个字段是否满足约束非空，唯一性，而且自动建立一个索引，并查看当把约束状态关闭再次插入相同的记录,是否还能把把约束设为enable ividate状态。
　　SQL>. create table t(i number constraint pk_i primary key,v number).
　　SQL>. insert into t values(1,2).
　　SQL>. insert into t values(3,4).
　　SQL>. commit.
　　SQL>. 0select * from t.
　　I V
　　---------- ---------------------------
　　1 2
　　3 4
　　现在表中有两条记录，然后给它插主键为空或相同的值
　　SQL>. insert into t values(,10).
　　ERROR at line 1:
　　ORA-01400: cannot insert NULL into ("Y"."T"."I")
　　SQL>. insert into t values(1,10).
　　ERROR at line 1:
　　ORA-00001: unique constraint (Y.PK_I) violated
　　可以看到全部报错，此时主键不能为空或重复
　　查看是否建立索引
　　SQL>. 0select index_name from user_indexes.
　　INDEX_NAME
　　------------------------------
　　PK_I
　　把约束关闭再次做同样的操用
　　SQL>. alter table t disable novalidate constraint pk_i.
　　Table altered.
　　SQL>. insert into t values(,10).
　　1 row created.
　　SQL>. insert into t values(1,10).
　　1 row created.
　　SQL>. commit.
　　Commit complete.
　　SQL>. 0select * from t.
　　I V
　　---------- ----------
　　1 2
　　3 4
　　10
　　110
　　SQL>. 0select index_name from user_indexes.
　　no rows 0selected
　　可见当把约束关闭后就可以何意给表插数据了，而具索引也自动删除了。
　　现在激活约束
　　SQL>. alter table t enable validate constraint pk_i.
　　alter table t enable validate constraint pk_i
　　ERROR at line 1:
　　ORA-02437: cannot validate (SYS.PK_I) - primary key violated
　　因为表中主键有相同的值所以不能恢复到enable validate状态了
　　再次测试回复到enable novalidate
　　SQL>. alter table t enable novalidate constraint pk_i.
　　alter table t enable validate constraint pk_i
　　ERROR at line 1:
　　ORA-02437: cannot validate (SYS.PK_I) - primary key violated
　　也失败了,
　　因为表中主键有了空值和相同的值,所以恢复不到enable validate状态,但enable novalidate不检查旧数据所以应该还能恢复到enable novalidate.
　　要想恢复到enable novalidate必须建立主键索引(关闭约束时自动删除的那个索引)如下:
　　SQL>. create index pk_i on t(i).
　　Index created.
　　然后恢复到enable disvalidate,以后再插数据不能为空,主键也不能重复了.
　　SQL>. alter table t enable novalidate constraint pk_i.
　　Table altered.
　　SQL>. insert into t values(1,14).
　　insert into t values(1,14)
　　ERROR at line 1:
　　ORA-00001: unique constraint (SYS.PK_I) violated
　　2.修正约束数据
　　当给一个表作主键约束时,因为已存数据不满足约束规则,会提示错误信息,些时必须对数据进行修正
　　要修正数据先找出不满足约束的数据
　　如下表,有不满足约束的数据
　　SQL>. 0select * from t.
　　I V
　　---------- ------------------------
　　1 2
　　3 4
　　1512
　　15 10
　　如果一个表数据量多可通过如下方法查找
　　SQL>. alter table t 0drop constraint pk_i.
　　Table altered.
　　SQL>.conn y / 123
　　SQL>. @$ORACLE_HOME/rdbms/admin/utlexcpt.sql
　　Table created.
　　SQL>. alter table t add constraint pk_i primary key (i) exceptions into exceptions.
　　0select * from t where rowid in (0select row_id from exceptions)
　　I V
　　---------- ------------------------
　　1512
　　1510
　　找到了重复的记录
　　修正
　　SQL>.0update t set i=10 where v=12.
　　SQL>. 0select * from t.
　　I　　　　　　　V
　　---------- ----------
　　1　　　　　　　2
　　3　　　　　　　4
　　10　　　　　　　12
　　15　　　　　　　10
　　再建主键约束
　　alter table t add constraint pk_i primary key (i)
　　Table altered.
　　成功了!!!