Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Conditional UNIQUE constraint - is this possible?

Jacob MadsenOct 10 2002
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!
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Nov 8 2002
Added on Oct 10 2002
3 comments
528 views