oracle db中具有多个字段的条件唯一约束

2022-01-20 00:00:00 sql unique conditional oracle unique-constraint

我有这张桌子:

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

我想创建一个唯一的约束，仅当 status_x (obj_x, date_x, type_x)5.

I wanna create a unique constraint that applies to the fields (obj_x, date_x, type_x) only when status_x <> 5.

我试图创建这个，但 Oracle 说:

I have tried to create this one but Oracle says:

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));

正确的语法是什么?

推荐答案

@jamesfrj: 看起来您正在尝试确保您的表应该只包含一条记录 status <>5.

@jamesfrj: it looks like you are trying to ensure that your table should contain only one record for which status <>5.

您可以尝试通过连接列来创建唯一的功能索引，如下所示

You can try creating a unique functional index by concatenating the columns, as given below

      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);

希望对你有帮助

维沙德

相关文章