CODEWITHSUNDEEP
databaseoracle-databaseconstraintsunique-constraint
p45
p45

Reputation: 141

ORACLE - CHECK constraint enforcing conditional uniqueness

I have a table - PROGRAM - which includes the columns PROGRAM_NAME and PROGRAM_NUMBER.

PROGRAM_NUMBER is already unique.

CREATE UNIQUE INDEX UNIQUE_PROG_NUMBER on PROGRAM(upper(PROGRAM_NUMBER));

There is also a constraint to ensure that either PROGRAM_NAME or PROGRAM_NUMBER is always populated.

ALTER TABLE PROGRAM ADD CONSTRAINT NAME_OR_NUMBER_NOT_NULL
CHECK (PROGRAM_NAME IS NOT NULL OR PROGRAM_NUMBER IS NOT NULL) ENABLE;

Now a requirement has come through to ensure that, if PROGRAM_NUMBER is null, then PROGRAM_NAME must be unique.

Is this possible to enforce in a CHECK constraint?

Upvotes: 0

Views: 453

Answers (1)

Justin Cave
Justin Cave

Reputation: 231651

You can define this sort of conditional unique constraint using a function-based unique index.

CREATE UNIQUE INDEX idx_unique_program_name
    ON program( CASE WHEN program_number IS NULL
                     THEN program_name
                     ELSE NULL
                  END );

This takes advantage of the fact that Oracle doesn't index NULL values so the index that gets created only has entries for the rows where program_number IS NULL. It also avoids the possibility that combining strings produces a resulting string that is too long or that the first string has a suffix that is also a prefix for the other string.

Upvotes: 4

Related Questions