You have a parent table called persons with a primary key id. You have students and teachers tables that inherit from persons.
You have another table called orders and it has an attribute person_id.
You'd like to add a foreign_key constraint from persons.id to orders.id. Such that anytime you inserted a student or teacher record it would maintain this constraint. However postgresql will not allow this as the record must exist in the base class.
Request:
Optionally allow a way to maintain this foreign_key constaint at the application layer (MTI)
For instance, if you tried to insert an orders record and the person did exist in the persons table then MTI would throw a foreign constraint exception.
Postgresql does not allow for foreign key constraints from a base table to some other table. See Caveats section in https://www.postgresql.org/docs/9.5/static/ddl-inherit.html
Scenario:
You have a parent table called
persons
with a primary keyid
. You havestudents
andteachers
tables that inherit frompersons
.You have another table called
orders
and it has an attributeperson_id
.You'd like to add a foreign_key constraint from
persons.id
toorders.id
. Such that anytime you inserted astudent
orteacher
record it would maintain this constraint. However postgresql will not allow this as the record must exist in the base class.Request:
Optionally allow a way to maintain this foreign_key constaint at the application layer (MTI)
For instance, if you tried to insert an
orders
record and the person did exist in thepersons
table then MTI would throw a foreign constraint exception.