Oracle表达式唯一约束

Question

Oracle是否支持像这样使用表达式的约束呢？

注意Z = 'N'

ALTER TABLE A ADD CONSTRAINT U_A_KEY UNIQUE(X,Y,Z = 'N');

这个“唯一约束”是否可行？

例子：

INSERT INTO A VALUES('X','Y','N');  --OK
INSERT INTO A VALUES('X','Y','Y');  --OK
INSERT INTO A VALUES('X','Y','Y');  --OK
INSERT INTO A VALUES('X','Y','N');  --VOLIATION

- JARC

---

这是否意味着您希望每个x，y组合最多只有一条Z ='N'的记录？ - René Nyffenegger

Answer 1

也许这会给你一个想法

drop table tq84_n;

create table tq84_n (
   x number, 
   y number, 
   z varchar2(10)
);

create unique index tq84_n_x on tq84_n (
  case when z = 'N' then x || '-' || y 
       else null
  end
);

稍后：

insert into tq84_n values (4,5, 'N');

insert into tq84_n values (9,6, 'Y');
insert into tq84_n values (9,6, 'Y');

insert into tq84_n values (4,5, 'Y');

insert into tq84_n values (4,5, 'N');

最后一个抛出异常：

ORA-00001: unique constraint (SPEZMDBA.TQ84_N_X) violated

- René Nyffenegger

Answer 2

在这种情况下，最简单的方法通常是创建一个基于函数的索引。类似于：

在这种情况下，最简单的方法通常是创建一个基于函数的索引。类似于：

CREATE UNIQUE INDEX u_a_key
    ON a( (CASE WHEN z = 'N' THEN x ELSE null END),
          (CASE WHEN z = 'N' THEN y ELSE null END) );

如果 z 不等于 'N'，那么两个 CASE 语句都会被评估为 NULL，Oracle 就不必将 x 和 y 的值存储在索引结构中（从而使索引变小）。如果 z 等于 'N'，则 x 和 y 的值都存储在索引中，索引的行为就像任何其他复合索引一样。

- Justin Cave

Answer 3

在这种情况下，我会创建一个列，例如Z，其中包含：

• 特定值（例如您的“N”），以防需要它是唯一的
• 否则为空，表示未知：两个未知值被认为不相等。

然后，您可以创建唯一约束UNIQUE(X,Y,Z)。

添加两行具有相同的X和Y和Z =“N”，您将收到错误；添加两行具有相同的X和Y，均具有Z = null，则不会出现错误。

- Adrian Smith

---

Z列可以容纳另一个值，该值必须通过约束条件。问题已更新，附带SQL以澄清，谢谢。 - JARC