ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle 约束的状态

oracle 约束的状态

原创 Linux操作系统 作者:JumFlow 时间:2011-07-05 16:06:55 0 删除 编辑
enable validate:是默认,新旧数据同时满足约束规则
enable novalidate:旧数据可以不满足,检新插入的数据要满足约束
disable validate:不允许在表上执行任何DML操作,主要用在分区表,对于主键和唯一约事,会删除相应的唯一索引,但约束状态任可用
disable novalidate数据可不满足约束规则,对于主键和唯一约事,会删除相应的唯一索引

=========================================================
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 drop constraint i;
  alter table t drop 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> select * 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> select 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> select * from t;
  I V
  ---------- ----------
  1 2
  3 4
  10
  110
  SQL> select index_name from user_indexes;
  no rows selected
  可见当把约束关闭后就可以何意给表插数据了,而具索引也自动删除了。
  现在激活约束
  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> select * from t;
  I V
  ---------- ------------------------
  1 2
  3 4
  1512
  15 10
  如果一个表数据量多可通过如下方法查找
  SQL> alter table t drop 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;
  select * from t where rowid in (select row_id from exceptions)
  I V
  ---------- ------------------------
  1512
  1510
  找到了重复的记录
  修正
  SQL>update t set i=10 where v=12;
  SQL> select * from t;
  I       V
  ---------- ----------
  1       2
  3       4
  10       12
  15       10
  再建主键约束
  alter table t add constraint pk_i primary key (i)
  Table altered.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25380220/viewspace-701387/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-03-03

  • 博文量
    72
  • 访问量
    185518