目的： 测试一下， 1）如果一个表中已经创建了唯一索引，那么再在唯一索引上创建唯一键约束和主键约束，与直接创建主键约束与唯一键约束的区别 2）如果一个表中已经创建了普通索引，那么再在普通索引上创建唯一键约束和主键约束，与直接创建主键约束与唯一键约束的区别
主键、唯一键约束、唯一索引区别之创建 /685769/viewspace-743314 主键、唯一键约束、唯一索引区别之删除 /685769/viewspace-743403 主键、唯一键约束、唯一索引区别之修改 /685769/viewspace-743328
一：创建测试表t1，并创建唯一索引 SCOTT@ORA11GR2>create table t1 ( a int, b int );
Table created.
SCOTT@ORA11GR2> SCOTT@ORA11GR2>create unique index uidx_t1_a on t1(a);
Index created.
SCOTT@ORA11GR2>create unique index uidx_t1_b on t1(b);
Index created.
SCOTT@ORA11GR2>
二：利用ctas方式通过t1表创建t2表 SCOTT@ORA11GR2>create table t2 as select * from t1;
Table created.
SCOTT@ORA11GR2> 此时要非常注意，ctas方式只创建了表的结构，相关的定义信息都没有，比如索引约束等，通过如下语句可以验证 SCOTT@ORA11GR2>select table_name,column_name,index_name from user_ind_columns where table_name=upper('t1');
TABLE_NAME COLUMN_NAME INDEX_NAME --------------- --------------- ------------------------------ T1 A UIDX_T1_A T1 B UIDX_T1_B
SCOTT@ORA11GR2> SCOTT@ORA11GR2>select table_name,column_name,index_name from user_ind_columns where table_name=upper('t2');
no rows selected
SCOTT@ORA11GR2>
三：给t2表创建普通索引 SCOTT@ORA11GR2>create index idx_t2_a on t2(a);
Index created.
SCOTT@ORA11GR2>create index idx_t2_b on t2(b);
Index created.
SCOTT@ORA11GR2>
四：检查t1表和t2表的索引情况，t1表创建的是唯一索引，t2表创建的是普通索引 SCOTT@ORA11GR2>select index_name,uniqueness,tablespace_name from user_indexes where table_name=upper('t1');
INDEX_NAME UNIQUENES TABLESPACE_NAME ------------------------------ --------- ------------------------------ UIDX_T1_B UNIQUE USERS UIDX_T1_A UNIQUE USERS
SCOTT@ORA11GR2>select index_name,uniqueness,tablespace_name from user_indexes where table_name=upper('t2');
INDEX_NAME UNIQUENES TABLESPACE_NAME ------------------------------ --------- ------------------------------ IDX_T2_B NONUNIQUE USERS IDX_T2_A NONUNIQUE USERS
SCOTT@ORA11GR2>
五：在t1表和t2表的a字段上创建主键约束 SCOTT@ORA11GR2>alter tablet1add constraint pk_t1_a primary key(a);
Table altered.
SCOTT@ORA11GR2>alter tablet2add constraint pk_t2_a primary key(a);
Table altered.
SCOTT@ORA11GR2>
六：在t1表和t2表的b字段上创建唯一键约束 SCOTT@ORA11GR2>alter tablet1add constraint uk_t1_a unique(b);
Table altered.
SCOTT@ORA11GR2>alter tablet2add constraint uk_t2_a unique(b);
Table altered.
SCOTT@ORA11GR2>
七：验证一下t1和t2的约束和索引情况 我们发现，t1和t2的约束都已创建成功，不过，相应的索引都没有改变，还是启用原来的索引，并未新创建默认的唯一索引 SCOTT@ORA11GR2>SELECT t.table_name, t.column_name, d.constraint_type, d.constraint_name FROM user_constraints d, user_cons_columns t WHERE d.constraint_name = t.constraint_name AND t.table_name =upper('t1');
TABLE_NAME COLUMN_NAME CONSTRAINT_TYPE CONSTRAINT_NAME --------------- --------------- ------------------ -------------------- T1 B U UK_T1_A T1 A P PK_T1_A
SCOTT@ORA11GR2> SCOTT@ORA11GR2>SELECT t.table_name, t.column_name, d.constraint_type, d.constraint_name FROM user_constraints d, user_cons_columns t WHERE d.constraint_name = t.constraint_name AND t.table_name =upper('t2');
TABLE_NAME COLUMN_NAME CONSTRAINT_TYPE CONSTRAINT_NAME --------------- --------------- ------------------ -------------------- T2 B U UK_T2_A T2 A P PK_T2_A
SCOTT@ORA11GR2> SCOTT@ORA11GR2>select index_name,uniqueness,tablespace_name from user_indexes where table_name=upper('t1');
INDEX_NAME UNIQUENES TABLESPACE_NAME ------------------------------ --------- ------------------------------ UIDX_T1_B UNIQUE USERS UIDX_T1_A UNIQUE USERS
SCOTT@ORA11GR2>select index_name,uniqueness,tablespace_name from user_indexes where table_name=upper('t2');
INDEX_NAME UNIQUENES TABLESPACE_NAME ------------------------------ --------- ------------------------------ IDX_T2_B NONUNIQUEUSERS IDX_T2_A NONUNIQUEUSERS
SCOTT@ORA11GR2>
八：t1表插入测试数据 SCOTT@ORA11GR2>insert into t1 values(null,1); insert into t1 values(null,1) * ERROR at line 1: ORA-01400: cannot insert NULL into ("SCOTT"."T1"."A")
SCOTT@ORA11GR2>insert into t1 values(1,null);
1 row created.
SCOTT@ORA11GR2>insert into t1 values(2,1);
1 row created.
SCOTT@ORA11GR2>commit;
Commit complete.
SCOTT@ORA11GR2>select * from t1;
AB ---------- ---------- 1 21
SCOTT@ORA11GR2>insert into t1 values(2,2); insert into t1 values(2,2) * ERROR at line 1: ORA-00001: unique constraint (SCOTT.PK_T1_A) violated
SCOTT@ORA11GR2>insert into t1 values(3,1); insert into t1 values(3,1) * ERROR at line 1: ORA-00001: unique constraint (SCOTT.UK_T1_A) violated
SCOTT@ORA11GR2> t1表验证结果： 主键无法插入null值，唯一键可以插入null值，主键和唯一键都不能重复
九：t2表插入测试数据 SCOTT@ORA11GR2>insert into t2 values(null,1); insert into t2 values(null,1) * ERROR at line 1: ORA-01400: cannot insert NULL into ("SCOTT"."T2"."A")
SCOTT@ORA11GR2>insert into t2 values(1,null);
1 row created.
SCOTT@ORA11GR2>insert into t2 values(2,1);
1 row created.
SCOTT@ORA11GR2>commit;
Commit complete.
SCOTT@ORA11GR2>select * from t2;
AB ---------- ---------- 1 21
SCOTT@ORA11GR2>SCOTT@ORA11GR2>insert into t2 values(2,2); insert into t2 values(2,2) * ERROR at line 1: ORA-00001: unique constraint (SCOTT.PK_T2_A) violated
SCOTT@ORA11GR2>insert into t2 values(3,1); insert into t2 values(3,1) * ERROR at line 1: ORA-00001: unique constraint (SCOTT.UK_T2_A) violated
SCOTT@ORA11GR2> t2表验证结果同t1表
十：删除约束 1）删除t1约束 SCOTT@ORA11GR2>alter table t1 drop constraint uk_t1_a;
Table altered.
SCOTT@ORA11GR2>alter table t1 drop constraint pk_t1_a;
Table altered.
SCOTT@ORA11GR2> SCOTT@ORA11GR2>SELECT t.table_name, t.column_name, d.constraint_type, d.constraint_name FROM user_constraints d, user_cons_columns t WHERE d.constraint_name = t.constraint_name AND t.table_name = upper('t1');
no rows selected
SCOTT@ORA11GR2> SCOTT@ORA11GR2>select index_name,uniqueness,tablespace_name from user_indexes where table_name=upper('t1');
INDEX_NAME UNIQUENES TABLESPACE_NAME ------------------------------ --------- ------------------------------ UIDX_T1_B UNIQUE USERS UIDX_T1_A UNIQUE USERS
SCOTT@ORA11GR2>
2）删除t2约束SCOTT@ORA11GR2>alter table t2 drop constraint uk_t2_a cascade;
Table altered.
SCOTT@ORA11GR2>alter table t2 drop primary key;
Table altered.
SCOTT@ORA11GR2> SCOTT@ORA11GR2>SELECT t.table_name, t.column_name, d.constraint_type, d.constraint_name FROM user_constraints d, user_cons_columns t WHERE d.constraint_name = t.constraint_name AND t.table_name = upper('t2');
no rows selected
SCOTT@ORA11GR2> SCOTT@ORA11GR2>select index_name,uniqueness,tablespace_name from user_indexes where table_name=upper('t2');
INDEX_NAME UNIQUENES TABLESPACE_NAME ------------------------------ --------- ------------------------------ IDX_T2_B NONUNIQUE USERS IDX_T2_A NONUNIQUE USERS
SCOTT@ORA11GR2> 注：我印象中，在删除唯一约束的时候，加上cascade子句，那么，会级联删除相关的索引，可以通过最近的测试发现，如果索引是唯一约束自动创建的，那么不加cascade也能将索引删除，如果在已存在的索引上创建唯一约束，那么加不加cascade都无法级联删除索引，这个可以理解，因为那个索引本身就不是你创建的，可是自动创建的索引怎么跟我以前测试的不同了，难道我记错了？
小结： 1）在已存在索引的基础上创建约束，无论已存在的索引是唯一的还是普通的，那么他们的约束都是起作用的（不过，普通索引和唯一索引上创建的约束，在生成执行计划的时候，应该是不同的），也就是说，主键约束和唯一键约束的唯一性不单单是有唯一索引控制的，本身主键约束和唯一键约束就有控制唯一的能力。 2）删除在已存在索引上创建的约束，那么只删除约束，对索引没有什么影响。 3）第十步中删除主键的方法有两种，一种是利用主键约束的名称删除，另一种是直接删除表中的主键（因为一张表中只允许有一个主键，索引，oracle也支持直接删除primary key）
来自 “ ITPUB博客 ” ，链接：/685769/viewspace-743405/，如需转载，请注明出处，否则将追究法律责任。
