unique index and constraint

最新推荐文章于 2019-01-07 09:50:38 发布
最新推荐文章于 2019-01-07 09:50:38 发布
阅读量157 收藏
点赞数
分类专栏： oracle 文章标签： 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/iteye_650/article/details/82131589
版权

1. 测试unique index 和 unique constraint

SYS@anqing2(rac2)> create table ut(idnumber,phone varchar2(15),name varchar2(15));

Table created. 

SYS@anqing2(rac2)> insert into utvalues(1,'13888888888','dave');

1 row created.

SYS@anqing2(rac2)> insert into utvalues(1,'13888888888','dave');

1 row created.

SYS@anqing2(rac2)> insert into utvalues(2,'13899999999','dba');

1 row created.

SYS@anqing2(rac2)> commit;

Commit complete.

 

--在phone 字段上，我们创建uniqueconstraint

SYS@anqing2(rac2)> alter table ut addconstraint uc_phone unique(phone);

alter table ut add constraint uc_phoneunique(phone)

                              *

ERROR at line 1:

ORA-02299: cannot validate (SYS.UC_PHONE) -duplicate keys found

--这里报错，因为我们在插入数据的时候，有重复值，先删除掉重复值

 

SYS@anqing2(rac2)> select * from ut;

 

       ID PHONE           NAME

---------- --------------- ---------------

        1 13888888888     dave

        2 13899999999     dba

        1 13888888888     dave

 

SYS@anqing2(rac2)> delete from ut whererownum=1;

1 row deleted.

 

SYS@anqing2(rac2)> commit;

Commit complete.

 

SYS@anqing2(rac2)> select * from ut;

       ID PHONE           NAME

---------- --------------- ---------------

        2 13899999999     dba

        1 13888888888     dave

 

--唯一性约束创建成功

SYS@anqing2(rac2)> alter table ut addconstraint uc_phone unique(phone);

Table altered.

 

--查看约束

SYS@anqing2(rac2)> selectconstraint_name,constraint_type,table_name,index_owner,index_name fromuser_constraints where table_name = 'UT';

 

CONSTRAINT_NAME C TABLE_NAME  INDEX_OWNER  INDEX_NAME

--------------- - -------------------------- -------------

UC_PHONE        U UT            SYS           UC_PHONE

--Oracle 自动创建了索引并关联到约束, 索引名和约束名是相同的。

 

--验证下索引

SYS@anqing2(rac2)> selectindex_name,index_type,uniqueness,generated from user_indexes wheretable_name='UT';

 

INDEX_NAME    INDEX_TYPE    UNIQUENES GENERATED

------------- ------------- -------------------

UC_PHONE      NORMAL        UNIQUE    N

--我们并没有创建索引，而是在创建unique constraint时，oracle 强制创建了uniqueindex。

 

--现在我们drop index 看看

SYS@anqing2(rac2)> drop index uc_phone;

drop index uc_phone

          *

ERROR at line 1:

ORA-02429: cannot drop index used forenforcement of unique/primary key

--这里报错，不能删除unique/primary key 上的索引。在这种情况下，我们只有先删除约束。

 

SYS@anqing2(rac2)> alter table ut dropconstraint uc_phone;

Table altered.

 

SYS@anqing2(rac2)> drop index uc_phone;

drop index uc_phone

          *

ERROR at line 1:

ORA-01418: specified index does not exist

--再次drop 索引时，提示索引已经不存在，说明已经在删除约束的同时，把索引删掉了。

 

SYS@anqing2(rac2)> selectconstraint_name,constraint_type,table_name,index_owner,index_name fromuser_constraints where table_name = 'UT';

no rows selected

 

SYS@anqing2(rac2)> selectindex_name,index_type,uniqueness,generated from user_indexes wheretable_name='UT';

no rows selected

 

结论：

       当约束列上没有索引时，在创建unique constraint 时，oracle 会自动创建unique index，并且该索引不能删除，当删除unique constraint 时，unique index 会自动删除。

 

 

2. 测试unique constraint 和non-unique index

 

--现在字段phone上创建B-Tree索引

SYS@anqing2(rac2)> create indexidx_ut_phone on ut(phone);

Index created.

 

--查看索引

SYS@anqing2(rac2)> selectindex_name,index_type,uniqueness,generated from user_indexes wheretable_name='UT';

INDEX_NAME    INDEX_TYPE    UNIQUENES GENERATED

------------- ------------- -------------------

IDX_UT_PHONE  NORMAL       NONUNIQUE N

 

--创建unique constraint

SYS@anqing2(rac2)>  alter table ut add constraint uc_phoneunique(phone);

Table altered.

 

--查看约束和索引信息

SYS@anqing2(rac2)> selectconstraint_name,constraint_type,table_name,index_owner,index_name fromuser_constraints where table_name = 'UT';

 

CONSTRAINT_NAME C TABLE_NAME   INDEX_OWNER  INDEX_NAME

--------------- - -------------------------- -------------

UC_PHONE        U UT            SYS           IDX_UT_PHONE

--这里重用了已经存在的索引

 

SYS@anqing2(rac2)> selectindex_name,index_type,uniqueness,generated from user_indexes wheretable_name='UT';

INDEX_NAME    INDEX_TYPE    UNIQUENES GENERATED

------------- ------------- -------------------

IDX_UT_PHONE  NORMAL       NONUNIQUE N

 

--删除索引

SYS@anqing2(rac2)> drop indexIDX_UT_PHONE;

drop index IDX_UT_PHONE

          *

ERROR at line 1:

ORA-02429: cannot drop index used forenforcement of unique/primary key

--这个提示和之前的一样，我们先删除约束，在来查看

 

SYS@anqing2(rac2)> alter table ut dropconstraint uc_phone;

Table altered.

 

SYS@anqing2(rac2)> select constraint_name,constraint_type,table_name,index_owner,index_namefrom user_constraints where table_name = 'UT';

no rows selected

--这里约束已经删除掉了。

 

SYS@anqing2(rac2)> selectindex_name,index_type,uniqueness,generated from user_indexes wheretable_name='UT';

INDEX_NAME    INDEX_TYPE    UNIQUENES GENERATED

------------- ------------- -------------------

IDX_UT_PHONE  NORMAL       NONUNIQUE N

--但是我们的索引并在删除约束时删除掉

 

--在手工删除索引，成功

SYS@anqing2(rac2)> drop indexIDX_UT_PHONE;

Index dropped.

 

SYS@anqing2(rac2)> selectindex_name,index_type,uniqueness,generated from user_indexes wheretable_name='UT';

no rows selected

 

--重新把约束和索引加上，然后一次删除

SYS@anqing2(rac2)> create indexidx_ut_phone on ut(phone);

Index created.

SYS@anqing2(rac2)> alter table ut addconstraint uc_phone unique(phone);

Table altered.

 

SYS@anqing2(rac2)> selectconstraint_name,constraint_type,table_name,index_owner,index_name fromuser_constraints where table_name = 'UT';

CONSTRAINT_NAME C TABLE_NAME    INDEX_OWNER   INDEX_NAME

--------------- - -------------------------- -------------

UC_PHONE        U UT            SYS           IDX_UT_PHONE

 

SYS@anqing2(rac2)> selectindex_name,index_type,uniqueness,generated from user_indexes wheretable_name='UT';

INDEX_NAME    INDEX_TYPE    UNIQUENES GENERATED

------------- ------------- -------------------

IDX_UT_PHONE  NORMAL       NONUNIQUE N

 

SYS@anqing2(rac2)> alter table ut drop constraint uc_phone drop index;

Table altered.

SYS@anqing2(rac2)> selectconstraint_name,constraint_type,table_name,index_owner,index_name fromuser_constraints where table_name = 'UT';

no rows selected

SYS@anqing2(rac2)> selectindex_name,index_type,uniqueness,generated from user_indexes wheretable_name='UT';

no rows selected

--索引和约束一次删除

 

 

小结：

       当我们的列上有索引时，在创建unique constraint时，Oracle 会重用之前的索引，并且不会改变索引的类型，在第一个测试里，Oracle 自动创建的索引是unique index。

       当我们删除约束时，关联的索引不会自动删除。 这个问题的MOS 上有说明。 参考MOS [ID309821.1]。

       我们可以分两步，先删除约束，在删除索引。 MOS 提供了方法，就是在删除约束时，加上drop index，这样就能一次搞定。

       SQL>altertable ut drop constraint uc_phone drop index;

iteye_650
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
SQL语句实现查询Index使用状况
09-10
- `--AND i.is_primary_key<>1` 和 `--AND i.is_unique_constraint<>1` 过滤掉主键和唯一约束索引。 5. **排序**： 通过`ORDER BY [Table Name],[index name]` 按照表名和索引名进行排序，使得结果更容易阅读和...
oracle删除主键查看主键约束及创建联合主键
09-10
ADD CONSTRAINT unique_employee_dept PRIMARY KEY (employee_id, department_id); ``` 请注意，创建联合主键后，这两个列的组合必须在表中是唯一的，且它们都不能为NULL。 总结来说，理解和操作主键是数据库管理...
unique constraint 和 unique index 的关系
weixin_30618985的博客
01-30 248
今天开发人员说，明明执行了删除唯一约束，但是插入数据的时候仍然遇到唯一性冲突。经过检查，发现是和约束同名的一个唯一索引造成的。为啥删除了唯一约束之后，索引还在？这个地方其实有点讲究，关键的区别就在于，索引是自动建立的还是手动建立的。 自动建立 默认的情况下，当我们建立一个unique constraint的时候，Oracle会自动的建立一个同名的unique index，真正...
SQL中使用UNIQUE Constraint还是Unique Index
XuXuemin的专栏
05-27 2236
当我需要在表格中增加一个必须被唯一定义的柱状表时，发现SQL Server允许在一个柱状表上定义一个UNIQUE constraint。但是我也可以在柱状表上创造一个Unique Index。哪一个是我应该使用的? 　　专家解答： 　　这两种方法之间的不同是很微妙的，UNIQUE constraints是ANSI SQL定义的一部分，定义UNIQUE constraints是数据库逻辑定义的一
Data Warehouse Guide阅读笔记（六）：unique constraint & unique index
csdn_201601的博客
02-09 103
作者: NinGoo(http://ningoo.itpub.net)发表于: 2006.10.25 10:10分类: DW&BI 出处: http://ningoo.itpub.net/post/2149/223696...
Oracle 唯一 约束（unique constraint） 与 索引（index） 关系说明
David Dai -- Focus on Oracle
07-13 1万+
一. 官网对Unique Constraints说明http://download.oracle.com/docs/cd/E11882_01/server.112/e16508/datainte.htm#CNCPT1642        uniquekey constraint
数据库基础出自本人亲手制作，11个字好难凑啊
09-26
tname VARCHAR(6), tage INT(3), tdate DATE NOT NULL, CONSTRAINT pk_tno PRIMARY KEY(tno), CONSTRAINT uk_tname UNIQUE(tname), CONSTRAINT check_tage CHECK(tage BETWEEN 0 AND 250))`，创建了一个包含主键、...
bbs论坛项目运用SSH框架Oracle数据库编写的一个简单示列项目
12-06
-- Create/Recreate primary, unique and foreign key constraints alter table USERS add constraint PK_USERID primary key (USERID) using index tablespace SYSTEM pctfree 10 initrans 2 maxtrans 255 ...
详细oracle笔记1
07-28
AND c.owner = cc.owner AND c.constraint_name = cc.constraint_name ORDER BY cc.position; ``` 5. **删除主键或外键**： 删除约束的命令是： ```sql ALTER TABLE table_name DROP CONSTRAINT key_name; ...
create unique index与alter .. add constraint .. unique的差异
N
07-09 5854
原由： 今天在rename表时,特别是表中包含主键索引和唯一索引时也需要将constraint进行rename； 否则在重建回原始表的主键名称时将报错； 类似如下：                                  * ERROR at line 1: ORA-02264: name already used by an existing constraint
unique constraint , unique index
jupiter_888的博客
01-07 90
image.png
MySQL——约束(constraint)详解
热门推荐
浅然的专栏
03-22 17万+
该博客说说关于数据库中一个重要的知识点——约束 一、什么是约束 约束英文：constraint 约束实际上就是表中数据的限制条件 二、约束作用 表在设计的时候加入约束的目的就是为了保证表中的记录完整和有效 比如name字段中要让其用户名不重复，这就需要添加约束。或者必须注册的时候需要添加邮箱等 三、约束种类 非空约束(not...
oracle带时分秒日期的比较
David
11-19 2811
oracle 中不带时分秒的字符串 to_date 转换时间后与带时分秒的date型字段做带等值的比较 (&gt;=,&lt;=,=)时，由于字符串不带时分秒，所以转换后时分秒默认为00:00:00,oracle中比较date是以一个时间点来比较的，精确到秒甚至毫秒微秒纳秒,所以date字段的时分秒不为00:00:00的就与不带时分秒字符串转换后的date匹配不上。示例： 表中huhai_s...
主键与外键之ORA-02292: integrity constraint
David
07-06 1592
今天在删除一个表某条记录时，出现如下错误：ORA-02292: integrity constraint (CICRO.FK8A82499F4C67C41) violated - child record found于是查看了相关的主键和约束关系，发现了一些问题。关于这个错误，oracle官方解决方法是：Error: orA-02292: integrity constraint &lt;cons...
你能把pg视图改为sqlserver的视图吗 SELECT c.relname AS table_name, a.attname AS column_name, d.description AS column_comment, CASE WHEN a.attnotnull AND con.conname IS NULL THEN 1 ELSE 0 END AS is_required, CASE WHEN con.conname IS NOT NULL THEN 1 ELSE 0 END AS is_pk, a.attnum AS sort, CASE WHEN "position"(pg_get_expr(ad.adbin, ad.adrelid), ((c.relname::text || '_'::text) || a.attname::text) || '_seq'::text) > 0 THEN 1 ELSE 0 END AS is_increment, btrim( CASE WHEN t.typelem <> 0::oid AND t.typlen = '-1'::integer THEN 'ARRAY'::text ELSE CASE WHEN t.typtype = 'd'::"char" THEN format_type(t.typbasetype, NULL::integer) ELSE format_type(a.atttypid, NULL::integer) END END, '"'::text) AS column_type FROM pg_attribute a JOIN (pg_class c JOIN pg_namespace n ON c.relnamespace = n.oid) ON a.attrelid = c.oid LEFT JOIN pg_description d ON d.objoid = c.oid AND a.attnum = d.objsubid LEFT JOIN pg_constraint con ON con.conrelid = c.oid AND (a.attnum = ANY (con.conkey)) LEFT JOIN pg_attrdef ad ON a.attrelid = ad.adrelid AND a.attnum = ad.adnum LEFT JOIN pg_type t ON a.atttypid = t.oid WHERE (c.relkind = ANY (ARRAY['r'::"char", 'p'::"char"])) AND a.attnum > 0 AND n.nspname = 'public'::name ORDER BY c.relname, a.attnum
最新发布
07-12
LEFT JOIN sys.key_constraints pk ON t.object_id = pk.parent_object_id AND ic.index_column_id = pk.unique_index_id WHERE t.type = 'U' AND SCHEMA_NAME(t.schema_id) = 'dbo' ORDER BY t.name, c.column_id `...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值