CODEWITHSUNDEEP
databaseoracle-databaseunique-constraint
orderlyfashion
orderlyfashion

Reputation: 613

Oracle unique index with null and special value allowed

I have a unique index today that allows all null values for column ssn, but if it has a value it has to be unique in combination with server.

Now I want to change it so that it both allow null values and a special defined value.

The old index was created like this:

CREATE UNIQUE INDEX UQ_SSN ON person (
    CASE WHEN ssn IS NULL THEN NULL ELSE server END,
    ssn);

Now I would like to change it into something like this:

CREATE UNIQUE INDEX UQ_SSN ON person (
    CASE WHEN (ssn IS NULL OR ssn = 'SPECIAL_VALUE') THEN NULL ELSE server END,
    ssn);

That doesn't work though. With that index I'm still allowed to add null-values. But I am only allowed to add one row where ssn = 'SPECIAL_VALUE', on the second one I get the error:

ORA-00001: unique constraint (APP_DB.UQ_SSN) violated

Upvotes: 0

Views: 489

Answers (1)

Popeye
Popeye

Reputation: 35900

You are getting the issue because when ssn = 'SPECIAL_VALUE' it creates the index on null, 'SPECIAL_VALUE' and while you insert new record then also it creates the index on null, 'SPECIAL_VALUE' which is not allowed.

You must use some other column like PK_COLUMN when there is ssn = 'SPECIAL_VALUE' so that null, pk_col will be different for each row.

Try case..when in the second column of the index.

CREATE UNIQUE INDEX UQ_SSN ON person (
    server,
    CASE WHEN ssn is null or ssn = 'SPECIAL_VALUE' THEN to_char(id) ELSE ssn END);

Note: You can use CASE .. WHEN without else. so I have slightly modified it.

Upvotes: 1

Related Questions