Conditional UNIQUE constraint - is this possible?
I have this (simplified) table:
CREATE TABLE MY_TABLE (
ID NUMBER(20) NOT NULL PRIMARY KEY,
TEXTFIELD VARCHAR2(200),
FOREIGNKEY_ID NUMBER(20),
REMOVED VARCHAR2(1) DEFAULT 'N'
CONSTRAINT CK1
CHECK (REMOVED IN ('Y','N') )
);
Previously, there was also a CONSTRAINT UQ1 UNIQUE (TEXTFIELD, FOREIGNKEY_ID). But the problem now is the following:
In some way, I need to expand the UNIQUE rule to include the REMOVED field. Unfortunately, that is ONLY if the REMOVED field has the value 'N' - a rule something like this: "if the value of the field REMOVED is 'N', the 2 fields TEXTFIELD and FOREIGNKEY_ID together must be unique; however, if the value of REMOVED is 'Y', duplicate values of TEXTFIELD and FOREIGNKEY_ID are allowed".
Is it possible, in some way, to include this condition in the UNIQUE constraint, or is there some better way of achieving this? Eventually by using CHECK constraints in some way? Feel free to ask, if there's some additional info needed.
I am using Oracle9i EE 9.0.1.
Thanks in advance!