PostgreSQL implements table inheritance. For example, you can create a places table (geographical objects) and several tables that will inherit all its columns. Suppose these are tables for information about cities ( cities ), rivers ( rivers ) and lakes ( lakes ):

CREATE TABLE places (id, name, latitude, longitude, altitude); CREATE TABLE cities (population) INHERITS (places); CREATE TABLE rivers (lenght) INHERITS (places); CREATE TABLE lakes (maxdepth, volume) INHERITS (places);

Now suppose that we want to store information on books about these objects in the database. The relationship between the object and the books is one-to-many . How can this be done if we actually have data in tables-descendants and at the same time the uniqueness of the keys is valid only within the same table?

UPD. No This logic is not supposed to work in PostgreSQL.

    2 answers 2

    As an option, do not use inheritance and go to traditional cities (place_id REFERENCES places.id , population) and sampling with JOINs. Less beautiful, convenient, but quickly and reliably.

    As another option - once PostgreSQL does not want to ensure the integrity of the keys, then ensure the integrity of the triggers or rules. You can use inheritance, but inserts and updates will be slower.

      Well, the triggers / keys can be used, of course, to check the integrity, or you can partition a table with books, hanging up as a check, for example, non-intersection by genre. Accordingly, foreign keys can be assigned from partition to partition. Here in Russian you can read a little more about table inheritance