Re: Unique/Primary key not inherited in partition, workaround?

Le 2012-11-07 à 13:58, Nicholas Wilson a écrit :

> Regarding the caveats here http://www.postgresql.org/docs/9.1/interactive/ddl-inherit.html#DDL-INHERIT-CAVEATS
>
> I am attempting to logically structure my location data. Say for example I have cities/states/countries. I have objects that reference a location, but at any level. An object may reference a city or it may reference a state, depending on how granular we know its location to be. If the city is known for an object, the state and country can be inferred, so the object need not point to all levels in fact that would be redundant and require checking consistency. Ideally, the object would have a foreign key reference to a generic "location" table (which would have child tables: cities, states, countries). The cities, states and countries could then have foreign keys pointing to each other (cities point to states, which point to countries).
>
> Does anyone know a workaround for my problem? Initially I was thinking a single locations table with a location "type" and a parent location id (references itself) although that's not ideal because I then have to do checks to ensure location type country does not end up being "contained" in a city. But perhaps I can write checks that ensure that never happens.

Could you use the following:

locations
Country, Region, City
USA, New York, New York
USA, New York, GLOBAL
USA, GLOBAL, GLOBAL
GLOBAL, GLOBAL, GLOBAL

If your locations table had that, then you can use foreign key checks to ensure the value is indeed present.

Bye,
François