UNIQUE constraint combining multiple columns with a condition

2
votes

I have a table in Oracle 11g such as with below columns.

  • COL1_STATUS
  • COL2_ID
  • COL3_TYPE
  • COL4_DATE

I want to create a UNIQUE constraint combining all 4 columns but only when COL1_STATUS = 10

How can I do that? Table is already created so I am looking for only ALTER command.

Also, I have searched and found similar question where it is suggested to use unique index but I want to achieve this by constraint.

Conditional unique constraint with multiple fields in oracle db

Thanks in advance.

1
sqloracleoracle11g

1 Answers

6
votes

A unique index and a constraint are essentially the same thing. A unique constraint is implement using a unique index. So this really should do what you want:

create unique index idx_table_4 on
    table(case when status = 10 then id end,
          case when status = 10 then type end,
          case when status = 10 then date end);

In fact, this is how the documentation recommends implementing a unique constraint:

When you specify a unique constraint on one or more columns, Oracle implicitly creates an index on the unique key. If you are defining uniqueness for purposes of query performance, then Oracle recommends that you instead create the unique index explicitly using a CREATE UNIQUE INDEX statement. You can also use the CREATE UNIQUE INDEX statement to create a unique function-based index that defines a conditional unique constraint. See "Using a Function-based Index to Define Conditional Uniqueness: Example" for more information.