CODEWITHSUNDEEP
sqlpostgresqlpsql
Vicens Fayos
Vicens Fayos

Reputation: 760

Unique constraints with null values as valid row

Hi I am inserting a bunch of data in a batch process.

The final output should be a table like this. Observe that here Name, Address_id pair are UNIQUE. Address_id can be NULL but only once for the given pair Name, Address_id

Name    address_id
———      —————
“Richard”   1
“Richard”   2
“Richard”   NULL
“Richard    3
“James”     1
“James”     2
“James”     NULL
“James”     3

However I can not guarantee the uniqueness of the pair when address_id is NULL. Another Pair (“Richard”, NULL) would be consider valid if I add an INDEX UNIQUE (name, address_id)

Then, if I add the following indexes (found it googling)

CREATE UNIQUE INDEX index_employers_on_name_and_address_id 
    ON employers(name, address_id) 
    WHERE address_id IS NOT NULL


CREATE UNIQUE INDEX index_employers_on_name 
    ON employers(name) 
    WHERE address_id IS NULL

All (<NAME>, NULL) pairs are not allowed because already exists a NAME unique

Some ideas?

Upvotes: 0

Views: 64

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

This index:

CREATE UNIQUE INDEX index_employers_on_name 
    ON employers(name) 
    WHERE address_id IS NULL ;

is a partial index that only applies when address_id is null. It will allow only one NULL value per name. Non-NULL values for address_id have no affect on the uniqueness.

This appears to do what you want.

Upvotes: 2

Related Questions