Oracle - only allow items to be inserted if three column values are unique and a fourth column is no-CodePudding

I am trying to enforce some uniqueness on data at the db level. For the sake of argument, my columns are:

ID
DocID (FK) 
FileName
FileRevision
Province
ActiveState

So in this example I want the combo of DocID, FIleName, and FileRevision entered only once. This is no issue, I can just create a UNIQUE constraint.

However the problem is, if 'ActiveState' is set to 'X', I want to allow any combo of those items.

In other words, only force uniqueness if ActiveState <> 'X'.

My understanding is I can not have a UNIQUE constraint with conditions. I've read about indexes, wondering if that's the path I need to travel?

I'm no Oracle expert (obviously), just looking for a tip!

CodePudding user response：

You can use virtual columns and then create a unique constraint on the virtual columns:

CREATE TABLE table_name (
  ID           NUMBER PRIMARY KEY,
  DocID        NUMBER,
  FileName     VARCHAR2(10),
  FileRevision NUMBER,
  Province     NUMBER,
  ActiveState  VARCHAR2(1),
  ActiveDocID  GENERATED ALWAYS AS (CASE ActiveState WHEN 'X' THEN NULL ELSE DocID END),
  ActiveFileName
               GENERATED ALWAYS AS (CASE ActiveState WHEN 'X' THEN NULL ELSE FileName END),
  ActiveFileRevision
               GENERATED ALWAYS AS (CASE ActiveState WHEN 'X' THEN NULL ELSE FileRevision END),
  CONSTRAINT table_name__uniq UNIQUE (
    ActiveDocID,
    ActiveFileName,
    ActiveFileRevision
  )
);

CodePudding user response：

For the same, Oracle provides the Conditoinal Index concept. You can create an conditional index on your table like below -

CREATE UNIQUE INDEX idx_ActiveState
    ON YOUR_TABLE (CASE WHEN ActiveState <> 'X' THEN DocID ELSE NULL END,
                   CASE WHEN ActiveState <> 'X' THEN FIleName ELSE NULL END,
                   CASE WHEN ActiveState <> 'X' THEN FileRevision ELSE NULL END);