Oracle唯一约束和唯一索引

Question

有人可以澄清没有唯一约束（Oracle）的情况下拥有唯一索引的目的是什么？ 例如，

create table test22(id int, id1 int, tmp varchar(20)); 
create unique index idx_test22 on test22(id); 
insert into test22(id, id1, tmp) values (1, 2, 'aaa'); // ok 
insert into test22(id, id1, tmp) values (1, 2, 'aaa'); // fails, ORA-00001: unique 
    // constraint (TEST.IDX_TEST22) violated 

到目前为止，它看起来像有一个约束。但是

create table test33(id int not null primary key, 
test22_id int not null, 
foreign key(test22_id) references test22(id)); 

也因为"ORA-02270: no matching unique or primary key for this column-list"而失败。 我完全被这种行为所困惑。有没有约束？

有许多文章解释了为什么可能有唯一的约束没有唯一的索引;这是清楚的，非常有意义。但是，我不明白没有约束的唯一索引的原因。

Answer 1

约束和索引是分离的逻辑实体。例如，唯一约束在USER_CONSTRAINTS（或ALL_CONSTRAINTS或DBA_CONSTRAINTS）中可见。索引在USER_INDEXES（或ALL_INDEXES或DBA_INDEXES）中可见。

一个唯一的约束是由一个索引强制执行的，虽然有可能（有时需要）使用非唯一索引强制执行一个唯一约束。例如，可延迟的唯一约束是使用非唯一索引实施的。如果您在列上创建非唯一索引并随后创建唯一约束，则还可以使用该非唯一索引来强制执行唯一约束。

实际上，唯一索引非常像一个独特的不可延迟约束，因为唯一约束的实现使用该索引，所以它引发了唯一约束引发的相同错误。但这并不完全相同，因为没有限制。所以，正如你所看到的，没有唯一的约束，所以你不能创建引用列的外键约束。

有些情况下，您可以创建一个唯一的索引，但不能创建唯一的约束。例如，基于函数的索引强制条件唯一性。如果我想创建一个支持逻辑删除表，但确保COL1是所有非删除的行唯一

SQL> ed 
Wrote file afiedt.buf 

    1 CREATE TABLE t (
    2 col1 number, 
    3 deleted_flag varchar2(1) check(deleted_flag in ('Y','N')) 
    4*) 
SQL>/

Table created. 

SQL> create unique index idx_non_deleted 
    2  on t(case when deleted_flag = 'N' then col1 else null end); 

Index created. 

SQL> insert into t values(1, 'N'); 

1 row created. 

SQL> insert into t values(1, 'N'); 
insert into t values(1, 'N') 
* 
ERROR at line 1: 
ORA-00001: unique constraint (SCOTT.IDX_NON_DELETED) violated 


SQL> insert into t values(1, 'Y'); 

1 row created. 

SQL> insert into t values(1, 'Y'); 

1 row created. 

但是，如果我们谈论的是一个直独特的非功能基指数，有可能是相对少数情况下，创建索引而不是创建约束确实更有意义。另一方面，在实践中差异很大的情况相对较少。你几乎从不想声明引用唯一约束而不是主键约束的外键约束，所以你很少会失去一些东西，只创建索引而不创建约束。

Answer 2

在这种情况下可能有用的另一点是： 禁用/删除现有唯一约束不会删除基础唯一索引。你必须明确地删除唯一索引。