understanding an inheritance in Postgres; why key "fails" in insert/update command

Question

(One image, tousands of words) I'd made few tables that are inherited between themselves. (persons) And then assign child table (address), and relate it only to "base" table (person). When try to insert in child table, and record is related to inherited table, insert statement fail because there is no key in master table. And as I insert records in descendant tables, records are salo available in base table (so, IMHO, should be visible/accessible in inherited tables). Please take a look on attached image. Obviously do someting wrong or didn't get some point.... Thank You in advanced!

Answer 1

Sorry, that's how Postgres table inheritance works. 5.10.1 Caveats explains.

A serious limitation of the inheritance feature is that indexes (including unique constraints) and foreign key constraints only apply to single tables, not to their inheritance children. This is true on both the referencing and referenced sides of a foreign key constraint. Thus, in the terms of the above example:

Specifying that another table's column REFERENCES cities(name) would allow the other table to contain city names, but not capital names. There is no good workaround for this case.

In their example, capitals inherits from cities as organization_employees inherits from person. If person_address REFERENCES person(idt_person) it will not see entries in organization_employees.

---

Inheritance is not as useful as it seems, and it's not a way to avoid joins. This can be better done with a join table with some extra columns. It's unclear why an organization would inherit from a person.

person
  id bigserial primary key
  name text not null
  verified boolean not null default false
  vat_nr text
  foto bytea

# An organization is not a person
organization
  id bigserial not null
  name text not null

# Joins a person with an organization
# Stores information about that relationship
organization_employee
  person_id bigint not null references person(id)
  organization_id bigint not null references organization(id)
  usr text
  pwd text

# Get each employee, their name, and their org's name.
select
  person.name
  organization.name
from
  organization_employee
join person on person_id = person.id
join organization on organization_id = organization.id

• Use bigserial (bigint) for primary keys, 2 billion comes faster than you think
• Don't enshrine arbitrary business rules in the schema, like how long a name can be. You're not saving any space by limiting it, and every time the business rule changes you have to alter your schema. Use the text type. Enforce arbitrary limits in the application or as constraints.
• idt_table_name primary keys makes for long, inconsistent column names hard to guess. Why is the primary key of person_address not idt_person_address? Why is the primary key of organization_employee idt_person? You can't tell, at a glance, which is the primary key and which is a foreign key. You still need to prepend the column name to disambiguate; for example, if you join person with person_address you need person.idt_person and person_address.idt_person. Confusing and redundant. id (or idt if you prefer) makes it obvious what the primary key is and clearly differentiates it from table_id (or idt_table) foreign keys. SQL already has the means to resolve ambiguities: person.id.