概述
目的：
测试一下，
1）如果一个表中已经创建了
唯一索引，那么再在
唯一索引上创建唯一键约束和主键约束，与直接创建主键约束与唯一键约束的区别
2）如果一个表中已经创建了
普通索引，那么再在
普通索引上创建唯一键约束和主键约束，与直接创建主键约束与唯一键约束的区别
主键、唯一键约束、唯一索引区别之创建
http://space.itpub.net/685769/viewspace-743314
主键、唯一键约束、唯一索引区别之删除
http://space.itpub.net/685769/viewspace-743403
主键、唯一键约束、唯一索引区别之修改
http://space.itpub.net/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 table
t1 add constraint pk_t1_a primary key(
a);
Table altered.
SCOTT@ORA11GR2>alter table
t2 add constraint pk_t2_a primary key(
a);
Table altered.
SCOTT@ORA11GR2>
六：在t1表和t2表的b字段上创建唯一键约束
SCOTT@ORA11GR2>alter table t1 add constraint uk_t1_a unique(b);
Table altered.
SCOTT@ORA11GR2>alter table
t2
add 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
NONUNIQUE USERS
IDX_T2_A
NONUNIQUE USERS
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;
A B
---------- ----------
1
2 1
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;
A B
---------- ----------
1
2 1
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博客 ” ，链接：http://blog.itpub.net/685769/viewspace-743405/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/685769/viewspace-743405/
最后
以上就是轻松香水为你收集整理的【约束】唯一键和主键在已存在索引的字段上创建和直接创建的区别的全部内容，希望文章能够帮你解决【约束】唯一键和主键在已存在索引的字段上创建和直接创建的区别所遇到的程序开发问题。
如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。
本图文内容来源于网友提供，作为学习参考使用，或来自网络收集整理，版权属于原作者所有。
发表评论 取消回复