Re: question about unique indexes

On 10 May 2010, at 2:09, Jonathan Vanasco wrote:

> i was given a unique index on
> (country_id, state_id, city_id, postal_code_id)

> in the two records below, only country_id and state_id are assigned ( aside from the serial )
>
> geographic_location_id | coordinates_latitude | coordinates_longitude | country_id | state_id | city_id | postal_code_id
> ------------------------+----------------------+-----------------------+------------+----------+---------+----------------
> 312 | | | 233 | 65 | |
> 443 | | | 233 | 65 | |
>
> i was under the expectation that the unique constraint would apply in this place.
>
> from the docs:
> When an index is declared unique, multiple table rows with equal indexed values are not allowed. Null values are not considered equal. A multicolumn unique index will only reject cases where all indexed columns are equal in multiple rows.

As the docs state and as others already mentioned, "Null values are not considered equal".

You're about to encounter an interesting problem. You have several optional foreign keys, so they have to be declared nullable, yet your constraints are such that you can't enforce uniqueness because nulls can't be compared.

You could (as mentioned by Al Rumman) create a unique index using coalesce() for each nullable column, but that index wouldn't be usable for normal queries - your query expressions won't match the indexes expressions ('WHERE city_id=7' vs. 'COALESCE(city_id, 0)=7'). I don't think the query planner sees the similarity between those expressions.

It is possible to add another index over those columns, without the coalesces, but it would take another time that amount of disk/memory space and the planner likely wouldn't be aware of the uniqueness of the data in it and hence not plan for that.

You could also change all your queries to use coalesce() for each of those columns. Hiding that in a view (maybe an updatable one so that CRUD operations can use the index too) would be a solution.

An alternative solution is to actually use 0 for those foreign keys and create a special record for that in the related tables. It depends on the table definitions how easy that is to do, you don't want to end up with all kinds of dummy-data in your client application, but you also don't want to remove any constraints that guarantee sensibility of the data in those tables.

None of these solutions are pretty. It should be quite a common problem though, how do people normally solve this?

Alban Hertroys

--
Screwing up is an excellent way to attach something to the ceiling.

!DSPAM:737,4be7e01210416358213314!