在Oracle数据库中，多个字段的条件唯一约束是什么？

Question

我有这个表格：

XPTO_TABLE (id, obj_x, date_x, type_x, status_x)

我想创建一个唯一约束，仅适用于(obj_x, date_x, type_x)字段，当且仅当status_x <> 5时。

我已经尝试创建这个约束，但Oracle报错：

line 1: ORA-00907: missing right parenthesis

CREATE UNIQUE INDEX UN_OBJ_DT_TYPE_STATUS
ON XPTO_TABLE(
    (CASE
         WHEN STATUS_X <> 5
         THEN
             (OBJ_X,
              TO_CHAR (DATE_X, 'dd/MM/yyyy'),
              TYPE_X)
         ELSE
             NULL
     END));

正确的语法是什么？

- James Freitas

---

我认为你需要将其设置为检查约束而不是唯一索引。 - Klas Lindbäck

@KlasLindbäck 我也这么认为，但他必须说出他想要检查的值。对于那3个字段没有值可检查的约束是没有意义的。如果有这样的约束，即使可能，也没有意义将其作为索引。 - Jorge Campos

@jamesfrj 我认为你应该更好地解释一下你需要什么。因为正如我在之前的评论中所说，你试图做什么是没有任何意义的。因此，请提供一些数据样本以及如果您尝试插入某些数据时应该受到的限制（我之所以说约束是因为你的问题）。 - Jorge Campos

大家好，我找到了一种方法，使用一个函数来连接字段的值，然后返回一个唯一的结果。然后我可以创建索引，如下所示：CREATE UNIQUE INDEX UN_OBJ_DT_TYPE_STATUS ON XPTO_TABLE (SUBSTR(FUN_UNIQUE_XPTO(OBJ_X, TYPE_X, DATE_X, CROB_IN_STATUS), 1, 20))。 - James Freitas

我根据http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1249800833250提供的解决方案进行了操作，但我认为有更好的解释。 - James Freitas

Answer 1

@jamesfrj: 看起来你想确保你的表只包含一个记录，其中status <>5。

你可以尝试通过连接列来创建一个唯一的函数索引，如下所示。

      create table XPTO_TABLE (id number, 
                            obj_x varchar2(20),
                            date_x date,
                            type_x varchar2(20),
                            status_x varchar2(20)                              
                           );

      create unique index xpto_table_idx1 on XPTO_TABLE(case when status_x <>'5'  THEN              obj_x||date_x||type_x||STATUS_x ELSE null END);

- vishad

---

这太过严格了 - 它可能会禁止一些行，即使它们不是真正的重复。 - Branko Dimitrijevic

@Vishad，这对我的情况来说是完美的解决方案。我之前已经创建了一个函数，但您的方法更加简洁。我现在正在使用它。非常感谢！ - James Freitas

我想给一个已存在的表添加唯一约束，但是出现了错误。ALTER TABLE T_EVENTATTENDEE ADD CONSTRAINT EVENT_ATTENDEE_UNIQUE UNIQUE (CASE WHEN C_DELETED = 0 THEN ( F_USER, F_EVENT ) ELSE null END);ORA-00907: 缺少右括号。 - majid_shoorabi

Answer 2

在Oracle 11下，您可以创建一组虚拟列，仅在STATUS_X为5时获得非NULL值，并使它们唯一。

CREATE TABLE XPTO_TABLE (
  ID INT PRIMARY KEY,
  OBJ_X INT,
  DATE_X DATE,
  TYPE_X VARCHAR2(50),
  STATUS_X INT,
  OBJ_U AS (CASE STATUS_X WHEN 5 THEN OBJ_X ELSE NULL END),
  DATE_U AS (CASE STATUS_X WHEN 5 THEN DATE_X ELSE NULL END),
  TYPE_U AS (CASE STATUS_X WHEN 5 THEN TYPE_X ELSE NULL END),
  UNIQUE (OBJ_U, DATE_U, TYPE_U)
);

只要STATUS_X不为5，您可以自由插入重复项：

INSERT INTO XPTO_TABLE (ID, OBJ_X, DATE_X, TYPE_X, STATUS_X) VALUES (1, 1, '1-JAN-2014', 'foo', 4);
INSERT INTO XPTO_TABLE (ID, OBJ_X, DATE_X, TYPE_X, STATUS_X) VALUES (2, 1, '1-JAN-2014', 'foo', 4);

当STATUS_X为5时，尝试插入重复数据会失败：

INSERT INTO XPTO_TABLE (ID, OBJ_X, DATE_X, TYPE_X, STATUS_X) VALUES (3, 1, '1-JAN-2014', 'foo', 5);
INSERT INTO XPTO_TABLE (ID, OBJ_X, DATE_X, TYPE_X, STATUS_X) VALUES (4, 1, '1-JAN-2014', 'foo', 5);

Error report -
SQL Error: ORA-00001: unique constraint (IFSAPP.SYS_C00139498) violated
00001. 00000 -  "unique constraint (%s.%s) violated"
*Cause:    An UPDATE or INSERT statement attempted to insert a duplicate key.
           For Trusted Oracle configured in DBMS MAC mode, you may see
           this message if a duplicate entry exists at a different level.
*Action:   Either remove the unique restriction or do not insert the key.

- Branko Dimitrijevic

---

这是一个不错的解决方案。谢谢分享！ - James Freitas

Answer 3

因为CREATE UNIQUE INDEX只期望一个值，所以您可以按以下方式连接列。

CREATE UNIQUE INDEX UN_OBJ_DT_TYPE_STATUS
ON XPTO_TABLE(
(CASE
     WHEN STATUS_X <> 5
         THEN OBJ_X || TO_CHAR (DATE_X, 'dd/MM/yyyy') || TYPE_X
     ELSE
         NULL
 END));

- Marco Montel

Answer 4

CREATE UNIQUE INDEX UN_MYID_uniq_IDX
ON MYTABLE(
(CASE
     WHEN MY_id > 1428923
         THEN MY_INDEX_COLUMN
     ELSE
         NULL
 END));

MY_INDEX_COLUMN 的值如果大于 1428923，则不能重复。

- Tadesse Mulat