Oracle主键约束自动建立索引问题

约束用于确保数据库数据的完整性,在Oracle数据库中,可以使用约束,触发器和应用代码(过程,函数)3种方法实现数据完整性,这3种方法中,因为约束易于维护,并且具有最好的性能,所以实现数据完整性首选约束.
 
一.约束分类
 
1.Not null： 确保字段值不能为空。 
 
2.Unique:  确保字段值唯一性，但不阻止多个空值的插入。 
 
3.Primary key：最常用的约束（主键约束），主键约束的列值不仅不能重复，也不能为NULL，注意一张表最多只能有一个主键约束，当定义主键约束后oracle自动建立一个以主键为关键字段的索引。
 
4.Foreign key:  定义了主从表之间的关系，foreign要定义在从表上，但主表必须具有主键约束或唯一约束，当定义froeign key后外部键列的数据必须在主表的主键列中存在，或者为NULL。 
 
5.Check:  用于强制表行数据必须满足的条件，如工资表，工人工资必须在2000-5000之间。 
 
 二.约束状态
 
enable validate:是默认,新旧数据同时满足约束规则
 
enable novalidate:旧数据可以不满足,检新插入的数据要满足约束
 
disable validate:不允许在表上执行任何DML操作,主要用在分区表,对于主键和唯一约事,会删除相应的唯一索引,但约束状态任可用
 
disable novalidate数据可不满足约束规则,对于主键和唯一约事,会删除相应的唯一索引 
 
三.建立主键约束产生索引
 检验当为一个表建立主键索引时后，这个字段是否满足约束非空，唯一性，而且自动建立一个索引，并查看当把约束状态关闭再次插入相同的记录后,是否还能把把约束设为enable validate状态。 
 
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);
 insert into t values(1,10)
 *
 ERROR at line 1:
 ORA-00001: unique constraint (SYS.PK_I) violated
 
可以看到全部报错，此时主键不能为空或重复

四.修正约束数据
 
当给一个表作主键约束时,因为已存数据不满足约束规则,会提示错误信息,些时必须对数据进行修正
 
要修正数据先找出不满足约束的数据
 
如下表,有不满足约束的数据
 
SQL> select * from t;       
 
  I          V
 
---------- ------------------------         
 
 1          2         
 
3          4         
 
15        12         
 
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;
 
SQL>select * from t where rowid in (select row_id from exceptions) ;
 
  I          V
 
 ---------- ------------------------         
 
15        12         
 
15        10
 
找到了重复的记录
 
修正
 
SQL>update t set i=10 where v=12;
 
SQL> select * from t;         
 
 I          V
 
---------- ----------         
 
 1          2         
 
 3          4         
 
 10        12         
 
15        10
 
再建主键约束
 
SQL>alter table t add constraint pk_i primary key (i) ;
 
 Table altered.
 
查看是否建立索引 
 
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
          1        10
 
                    10
 
 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 novalidate不检查旧数据所以应该还能恢复到enable novalidate，但为什么不行呢？因为在执行命令时，会产生唯一性索引（对pk_i),但对表中pk_i列已经有了重复的值，无法建立唯一性索引。
 
 
要想恢复到enable novalidate必须建立主键索引(关闭约束时自动删除的那个索引，非唯一)如下:
 
SQL> create index pk_i on t(i);
 
 Index created.
 
索引的默认类型是非唯一，非压缩，非反向键B*树索引。
 
 
然后恢复到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