测试一
10:44:13 SQL> select * from test;
A
--
1
1
2
Executed in 0.063 seconds
10:44:46 SQL> alter table test add constraint pk_a primary key(a);
alter table test add constraint pk_a primary key(a)
ORA-02437: cannot validate (SYS.PK_A) - primary key violated
10:45:02 SQL> alter table test add constraint pk_a primary key(a) novalidate;
alter table test add constraint pk_a primary key(a) novalidate
ORA-02437: cannot validate (SYS.PK_A) - primary key violated
10:45:23 SQL> create index idx_a on test(a);
Index created
Executed in 0.078 seconds
10:45:54 SQL> alter table test add constraint pk_a primary key(a) ;
Table altered
Executed in 0.109 seconds
10:45:57 SQL> select index_name,uniqueness from dba_indexes where table_nAME='TEST'
2 ;
INDEX_NAME UNIQUENESS
------------------------------ ----------
IDX_A NONUNIQUE
Executed in 0.172 seconds
10:46:01 SQL> insert into test values(1);
insert into test values(1)
ORA-00001: unique constraint (SYS.PK_A) violated
(1)原表test的a字段有重复的列,直接novalidate建主键 不可以
(2)先建普通索引idx_a,然后在上面使用novalidate建主 键,成功
(3)索引状态为NONUNIQUE索引,但是仍然具有unique约束
=======================================
测试2
11:02:25 SQL> select * from t2;
A
--
1
Executed in 0.032 seconds
11:04:38 SQL> create index idx_t2_a on t2(a);
Index created
Executed in 0.078 seconds
11:05:12 SQL> alter table t2 add constraint pk_t2 primary key(a);
Table altered
Executed in 0.25 seconds
11:05:20 SQL> select index_name,uniqueness from dba_indexes where table_nAME='T2'
2 ;
INDEX_NAME UNIQUENESS
------------------------------ ----------
IDX_T2_A NONUNIQUE
Executed in 0.188 seconds
11:12:35 SQL> insert into t2 values(1);
insert into t2 values(1)
ORA-00001: unique constraint (SYS.PK_T2) violated
(1)原表t2的a字段无重复的列
(2)先建普通索引idx_t2_a,然后在上面直接建主键,成功
(3)索引状态为NONUNIQUE索引,但是仍然具有unique约束
=======================================
11:15:50 SQL> create table t3(a number(1));
Table created
Executed in 0.125 seconds
11:31:45 SQL> alter table t3 add constraint pk_t3 primary key(a) ;
Table altered
Executed in 0.079 seconds
11:32:17 SQL> select index_name,uniqueness from dba_indexes where table_nAME='T3';
INDEX_NAME UNIQUENESS
------------------------------ ----------
PK_T3 UNIQUE
Executed in 0.172 seconds
直接建主键的情况
=======================================
结论:novalidate选项只避免校验unique约束,但是直接建主键如果无索引,会去尝试创建 unique索引,所以失败;先创建普通索引之后,novalidate选项避免校验了unique约束,但是因为有索引,无需去创建unique索引, 所以创建成功;
oracle创建主键两个步骤:建pk,建索引。没有索引,会建一个unique索引;有索引,只创建PK。但是没有unique索引但有Pk的字段仍有 unique约束。