Question:
There is a table with phone numbers:
create table phone (
client_id number(6) not null,
active number(1) not null check (active in (0, 1)),
value varchar2(15)
);
insert into phone (client_id, active, value) values (10, 0, '1111');
insert into phone (client_id, active, value) values (10, 1, '3333');
insert into phone (client_id, active, value) values (15, 0, '5555');
insert into phone (client_id, active, value) values (15, 1, '6666');
insert into phone (client_id, active, value) values (15, 0, '7777'); --ошибочная запись
Tried to ensure only one active phone per client using a unique index but couldn't get it to work.
When I try to create an index:
create unique index ix1 on phone (client_id, case when active = 1 then active end);
I get an error because the index entry is not unique:
Error: ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
SQLState: 72000
But it works fine in PostgreSQL .
Why doesn't it work in Oracle then?
Two inactive records will get NULL values from the CASE expression, would they be equal in Oracle? If we delete the last entry with the value 7777, then the index will be created.
Free translation of the question Is null equal to null in Oracle database? by @The Impaler
Answer:
Do this:
create unique index ix1 on phone (case active when 1 then client_id end);
A unique index does not save records only if the values of all columns are NULL . However, in this case, a function-based index results in one of the two columns being NULL , so the entry will be indexed, resulting in a duplicate value in the index.
If you change the index so that only one value derived from the active and client_id columns is indexed, i.e. so that records where active = 0 result in a NULL value from the CASE expression, then those records will not be included in the index, and only active records will be checked for duplicates.
This is mentioned in sub. Unique Constraints Documentation :
To satisfy a unique constraint, no two rows in the table can have the same value for the unique key. However, the unique key made up of a single column can contain nulls . To satisfy a composite unique key, no two rows in the table or view can have the same combination of values in the key columns. Any row that contains nulls in all key columns automatically satisfies the constraint. However, two rows that contain nulls for one or more key columns and the same combination of values for the other key columns violate the constraint .
Free translation of answer from member @MT0