Conditional Unique Constraint with Multiple Fields in Oracle Db

Conditional unique constraint with multiple fields in oracle db

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

Hope it helps

Vishad

conditional unique constraint on multiple columns in oracle

You seem to want a 3-part index:

create unique index my_index
    on my_table (case when f_id <> 3 then c_id end,
                 case when f_id <> 3 then o_id end,
                 case when f_id <> 3 then f_id end
                );

UNIQUE constraint combining multiple columns with a condition

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.

Create conditional unique constraints on multiple columns

I believe this is sufficient:

create unique index <index_name> on <table_name> (case when a = 1 then <column_1> end, 
                                                  case when a = 1 then <column_2> end); 

Conditional unique constraint in oracle db

You can't create a constraint. But you can create a unique function-based index. This takes advantage of the fact that Oracle does not index NULL values-- any rows where isDeleted is NOT NULL will not be included in the index so the unique constraint won't apply to them.

CREATE UNIQUE INDEX one_not_deleted
    ON table_name( (CASE WHEN isDeleted IS NULL
                         THEN eid
                         ELSE null
                      END) );

Conditional Unique Constraint SQL

It sounds like you've tried to squash two or more tables in to one table.

• It's hard to tell without more background

For example, if you made a big flat file you might have this?

ALTER TABLE my_table ADD (
   draft_column1 <DATATYPE> GENERATED ALWAYS AS (DECODE(draft, 0, column1)),
   draft_column2 <DATATYPE> GENERATED ALWAYS AS (DECODE(draft, 0, column2)),
   draft_column3 <DATATYPE> GENERATED ALWAYS AS (DECODE(draft, 0, column3)),
   CONSTRAINT UNQ_02 UNIQUE(draft_column1, draft_column2, draft_column3)
);

CREATE UNIQUE INDEX UNQ_03 ON my_table (
   DECODE(draft, 0, column1),
   DECODE(draft, 0, column2),
   DECODE(draft, 0, column3)
);

CREATE UNIQUE INDEX table_name__status_name__u
  ON table_name( CASE status WHEN 'ON' THEN name END );

CREATE TABLE table_name (
  room_id NUMBER
          NOT NULL,
  name    VARCHAR2(10)
          NOT NULL,
  status  VARCHAR2(3)
          NOT NULL
          CHECK ( STATUS IN ( 'ON', 'OFF' ) )
);

INSERT INTO table_name ( room_id, name, status )
SELECT 1, 'alice', 'ON' FROM DUAL UNION ALL
SELECT 2, 'alice', 'OFF' FROM DUAL UNION ALL
SELECT 3, 'alice', 'OFF' FROM DUAL UNION ALL
SELECT 4, 'alice', 'OFF' FROM DUAL UNION ALL
SELECT 5, 'alice', 'OFF' FROM DUAL UNION ALL
SELECT 6, 'alice', 'OFF' FROM DUAL UNION ALL
SELECT 1, 'beatrice', 'OFF' FROM DUAL UNION ALL
SELECT 2, 'beatrice', 'OFF' FROM DUAL UNION ALL
SELECT 3, 'beatrice', 'OFF' FROM DUAL;

INSERT INTO table_name ( room_id, name, status )
SELECT 7, 'alice', 'ON' FROM DUAL;

INSERT INTO table_name ( room_id, name, status )
SELECT 4, 'beatrice', 'ON' FROM DUAL UNION ALL
SELECT 5, 'beatrice', 'ON' FROM DUAL;