Re: [Gnumed-devel] Backend dem.urb needs cleaner constraint

[Jim Busser]

On 2011-10-07, at 8:59 PM, Jim Busser wrote:

> Currently in dem.urb
> 
> Indexes:
>    "urb_pkey" PRIMARY KEY, btree (id)
>    "urb_id_state_key" UNIQUE, btree (id_state, postcode, name)
>    "idx_dem_urb_id_state" btree (id_state)
>    "idx_urb_names" btree (name)
>    "idx_urb_zips" btree (postcode)
> 
> 
> suggest
> 
>    "urb_id_state_key" UNIQUE, btree (id_state, postcode, name)
> 
> should better be
> 
>    "urb_id_state_key" UNIQUE, btree (id_state, UPPER(postcode), UPPER(name))


Likewise:

dem.state warrants to be
 
    "state_code_key" UNIQUE, btree (UPPER(code), UPPER(country))


dem.street warrants to be

    "street_id_urb_key" UNIQUE, btree (id_urb, UPPER(name), UPPER(postcode))

dem.address warrants to be

    "address_id_street_key" UNIQUE, btree (id_street, UPPER(aux_street), 
UPPER(number), UPPER(subunit), UPPER(addendum))


-- Jim