Add unique conditional constraint to exsiting table

Question

I want to add a unique conditional constraint to an existing table in oracle but I get an error.

ALTER TABLE T_EVENTATTENDEE ADD CONSTRAINT EVENT_ATTENDEE_UNIQUE UNIQUE (CASE WHEN C_DELETED = 0 THEN ( F_USER, F_EVENT ) ELSE null END);

and error

ORA-00907: missing right parenthesis

Answer 1

How about unique index instead? As you'd use a case expression, that would be a function-based index.

Sample table:

SQL> create table t_eventattendee
  2    (f_user    number,
  3     f_event   number,
  4     c_deleted number
  5    );

Table created.

Index:

SQL> create unique index ui1 on t_eventattendee
  2   (CASE WHEN C_DELETED = 0 THEN  F_USER||'~'||F_EVENT  ELSE null END);

Index created.

Testing:

SQL> insert into t_eventattendee(f_user, f_event, c_deleted) values (1, 1, 0);

1 row created.

SQL> insert into t_eventattendee(f_user, f_event, c_deleted) values (1, 1, 0);
insert into t_eventattendee(f_user, f_event, c_deleted) values (1, 1, 0)
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.UI1) violated


SQL> insert into t_eventattendee(f_user, f_event, c_deleted) values (1, 1, 5);

1 row created.

---

This is why there's a delimiter in index (this: F_USER||'~'||F_EVENT):

SQL> insert into t_eventattendee(f_user, f_event, c_deleted) values (2, 22, 0);

1 row created.

SQL> insert into t_eventattendee(f_user, f_event, c_deleted) values (22, 2, 0);

1 row created.

SQL>

Because, if we omit delimiter (see whether it makes sense; if data contains ~ character, use something that doesn't exist), then the above example - combination of [2, 22] and [22, 2] would evaluate to [222] in both cases which means that you'd get false unique index violation:

SQL> create unique index ui1 on t_eventattendee
  2   (CASE WHEN C_DELETED = 0 THEN  F_USER||F_EVENT  ELSE null END);   --> no delimiter

Index created.

SQL> insert into t_eventattendee(f_user, f_event, c_deleted) values (2, 22, 0);

1 row created.

SQL> insert into t_eventattendee(f_user, f_event, c_deleted) values (22, 2, 0);
insert into t_eventattendee(f_user, f_event, c_deleted) values (22, 2, 0)
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.UI1) violated


SQL>

Answer 2

According to the manual,

Oracle® Database
SQL Language Reference
19c
E96310-20
January 2023

adding a constraint uses the following syntax:

[ CONSTRAINT constraint_name ]
{ UNIQUE (column [, column ]...)
| PRIMARY KEY (column [, column ]...)
| FOREIGN KEY (column [, column ]...) references_clause
| CHECK (condition)
} [ constraint_state ]

so except for check-constraints you can only use a list of columns as argument.