【约束】唯一键和主键在已存在索引的字段上创建和直接创建的区别

最新推荐文章于 2021-03-27 15:23:24 发布
最新推荐文章于 2021-03-27 15:23:24 发布
阅读量757 收藏
点赞数
文章标签： 数据库
目的：
测试一下，
1）如果一个表中已经创建了 唯一索引，那么再在 唯一索引上创建唯一键约束和主键约束，与直接创建主键约束与唯一键约束的区别
2）如果一个表中已经创建了 普通索引，那么再在 普通索引上创建唯一键约束和主键约束，与直接创建主键约束与唯一键约束的区别 

主键、唯一键约束、唯一索引区别之创建
主键、唯一键约束、唯一索引区别之删除
主键、唯一键约束、唯一索引区别之修改

一：创建测试表 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/

clal800927001
关注
mysql系列之：建表实现主键自增、创建多个字段唯一约束、创建索引
zhengzaifeidelushang的博客
06-07 1003
mysql系列之：建表实现主键自增、创建多个字段唯一约束、创建索引
【MySQL】主键约束、唯一键约束以及外键约束的创建与删除
Sunnyside_
04-16 2194
【MySQL】主键约束、唯一约束以及外键约束的创建与删除 文章目录【MySQL】主键约束、唯一约束以及外键约束的创建与删除一、主键约束1.1 创建主键1.2 删除主键二、唯一键约束2.1 创建唯一键2.2 删除唯一键三、外键约束3.1 创建外键3.2 删除外键四、总结4.1 创建4.2 删除 一、主键约束   primary key用来唯一约束该字段里面的数据。其主要特征有： 不能重复，不能为空 一张表中最多只有一个primary key primary key 所在列通常是整数类型 1.1 创建主
主键与主键索引的关系
weixin_30379973的博客
01-15 204
http://www.51sdj.com 在oracle中，我们创建一个主键，则同时自动创建了一个同名的唯一索引；删除主键，则主键约束和对应的唯一索引都删除了。这是我们经常见到的现象。 发出一个创建主键的sql，oracle其实执行了两步：创建主键约束、创建/关联 唯一索引。步骤是这样的： 创建主键约束时，检查该主键字段上是否已经存在唯一索引。若不存在，则自动创建同名唯一索引；若存在，则...
创建主键约束时使用已有索引的好处
ciliting2867的博客
02-21 222
1. 在创建主键约束时，如果在主键列上没有索引，则会自动创建一个唯一索引： SQL> alter table test add constraint p_test primary key (empno); Table...
主键约束与其索引的问题
huangliang0703的专栏
09-22 578
1、如果索引是单独创建，那么在删除主键约束的时候，索引不会删除，除非使用alter table t drop constraint pk_t cascade drop index; 15:59:16 sys@CRMUAT>drop table t purge; Table dropped. 16:05:20 sys@CRMUAT>create table t (x int, y in
数据库中的索引
宋榄的博客
03-27 105
数据库中的索引 1.什么是索引？   索引相当于一本书的目录，通过目录可以快速的找到对应的资源，但是需要读者注意的是数据的修改，会导致索引需要重新的排序，这时就需要格外的人力进行数据的维护。 2.什么时候加索引   此时读者可能会问加了查询速度变快，但是会造成格外的开销。那在什么时候加索引合适呢？ 1. 数据量十分庞大 2. 该字段很少进行DML(数据操作语言)操作 3. 该字段经常出现在where子句中  主键和具有unique约束的字段会自动添加索引 3.如何创建/删除索引对象 <1
修改表字段的默认值
lckj2009的专栏
12-26 3883
修改表的默认值的方法大家都知道了，给表建立一个约束即可，如： ALTER   TABLE   TableName   add constraint ConstraintName default ('abc') for FieldName 一般做添加修改默认值的功能时还要判断这个字段上是不是已经有了约束了。如果有约束就必须在建立约束前先删除原来的约束，否则会报出： 消息 1781，级别 16，
SQL Server 创建约束图解(唯一 主键)
12-15
本文将详细讲解SQL Server中的五种主要约束：Primary Key（主键约束）、Foreign Key（外键约束）、Unique（唯一约束）、Default（默认约束）以及Check（检查约束），并结合SQL Server 2008来阐述它们的创建和使用...
SQLServer主键和唯一约束的区别
01-19
首先说明一点，主键又称主键约束，它也是一种约束，看下它和唯一约束的创建语法： alter table Person add constraint PK_Id primary key (Id) alter table Person add constraint UQ_Name unique (Name) 主键和...
mysql为字段添加和删除唯一性索引(unique) 的方法
12-16
主键索引是一种特殊的唯一索引，不允许有重复值且每个记录必须有一个值。它是表的标识符，用于确保数据的唯一性和完整性。添加主键索引的SQL语句如下： ```sql ALTER TABLE `table_name` ADD PRIMARY KEY ( `...
创建和修改 PRIMARY KEY 约束
geekczt
09-22 4825
可以在创建表时创建单个 PRIMARY KEY 约束作为表定义的一部分。如果表已存在，且没有 PRIMARY KEY 约束，则可以添加 PRIMARY KEY 约束。一个表只能有一个 PRIMARY KEY 约束。 如果已存在 PRIMARY KEY 约束，则可以修改或删除它。例如，可以让表的 PRIMARY KEY 约束引用其他列，更改列的顺序、索引名、聚集选项或 PRIMARY KEY
主键约束自动建立索引问题
布谷~~~Sun
06-17 4416
约束用于确保数据库数据的完整性,在oracle数据库中,可以使用约束,触发器和应用代码(过程,函数)3种方法实现数据完整性,这3种方法中,因为约束易于维护,并且具有最好的性能,所以实现数据完整性首选约束.     一.约束分类 1.Not null： 确保字段值不能为空。   2.Unique:  确保字段值唯一性，但不阻止多个空值的插入。   3.Primary key：最常用的
主键索引的创建
热门推荐
知也无涯
11-25 2万+
主键索引 当一张表,把某个列设为主键的时候,则该列就是主键索引 1:添加索引 1:主键索引(创建表时添加) CREATE TABLE Primary_key( id INT UNSIGNED COMMENT '无符号' PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(32) NOT NULL DEFAULT ' ');
MYSQL增加已有字段唯一索引，查看唯一约束，删除唯一约束
宇默
08-01 2207
增加已有字段唯一索引： alter table 表名称 add unique(需要添加唯一性的字段，也会作为唯一名称)； 查看唯一约束： show keys from 表名称; 删除唯一约束： drop index 索引名称 on 表名称;...
Mysql数据库中给表的字段创建约束时，自动给表加了一行关键字Key，撤销时没有把 KEY `fk_ClaStudents` (`c_id`) 删除
晓康的博客
08-20 1190
1.首先是创建两张表 Classes 和 Students CREATE TABLE Classes( c_id int NOT NULL, name varchar(200), PRIMARY KEY (c_id) ); CREATE TABLE Students( s_id int NOT NULL, name varchar(200), ...
创建Primary key主键和Unique唯一索引
大泡泡的专栏
10-28 3114
在MySQL中，我们进行数据存储时，为了避免插入重复数据，常常通过给数据表添加主键或者索引的方法来解决，在插入过程中，如果遇到重复数据就会提示“uplicate entry 'D550-49' for key 'cv_series'”的错误，防止我们插入重复的数据，我们可以结合 on duplicate key 方法来进行解决重复问题，将重复数据进行指定字段更新。 一、新建表的时候直接添加主键和Un
SQL Server 2008约束详解：主键与唯一性约束实践
主键和唯一性约束之间的区别在于： - 主键是表的标识，与表中的特定行密切相关，通常不易更改，且不允许为空。 - 唯一性约束则提供了一个可选的行访问方式，其值可以更新，且可能允许空值。 除了主键和唯一性约束，...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值