Propagate primary key to child tables

Question

I want to propagate the value of a primary key column from a parent table to a specific child table when inserting a new row.

For explanatory purposes I've created following tables:

Create TABLE Material(
MatID serial PRIMARY KEY,
materialname TEXT
);

Create TABLE RealMaterial(
MatID INT REFERENCES Material(MatID),
attributereal TEXT,
PRIMARY KEY(MatID)
);

Create TABLE VirtualMaterial(
MatID INT REFERENCES Material(MatID),
attributevirt TEXT,
PRIMARY KEY(MatID)
);

When I am inserting a new material I automatically want to add either a RealMaterial or a VirtualMaterial (referencing the new ID). I should emphasiZe that I want to use this shared-primary-key pattern and not just single-table-inheritance.

Should I use a trigger for my purposes?

Answer 1

From your comments, it seems that you are not actually concerned with "automatically inserting", but rather with "automatically enforcing" the presence¹ and exclusivity² of children.

Since PostgreSQL supports deferred constraints, you can do it declaratively like this.

However, it requires "uglyfying" your model and generally increases the complexity, so enforcing such constraints at the application level is often considered a lesser evil. You may consider hiding this kind of logic behind an API, to remove the possibility of "rogue" clients bypassing it.

---

¹ For given parent row, there must be a child row. In other words, parent class alone cannot be instantiated - it is abstract.

² For given parent row, there cannot be more than one child row.