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?

`either a RealMaterial or a VirtualMaterial` ... so the two extension tables are mutually exclusive, right? Also, how about [inheritance](http://stackoverflow.com/questions/21565183/create-a-table-of-two-types-in-postgresql/21565888#21565888) with a with a *shared `SEQUENCE`*? Is that off the table, too, or do you want me to outline a solution? — Erwin Brandstetter, Apr 29 '14 at 18:36
Yes, the two tables are mutually exclusive!! A material could be either a RealMaterial OR a VirtualMaterial but not both of them. Also you should not be allowed to create just a Material (like an abstract class). Inheritance is problematic because of the following reason: UNIQUE and PRIMARY KEYS are not passed through the hirarchy (see documentation). I found this answer: http://stackoverflow.com/questions/4361381/how-do-we-implement-an-is-a-relationship but in my opinion it is not a solution for implementing mutual exclusive behaviour. — Dede1989, Apr 29 '14 at 19:52
Some further research led to this method: http://dba.stackexchange.com/a/52255. Implementing a transaction and using currval() to propagate the newly primary key to the subtable. At least, because it is an atomic transaction, there should be no problem using currval(). But i am not happy with this solution. Maybe there are better methods? — Dede1989, Apr 30 '14 at 08:03

score 2 · Accepted Answer · edited May 23 '17 at 12:20

2

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.

edited May 23 '17 at 12:20

Community

1
1

answered Apr 29 '14 at 20:13

Branko Dimitrijevic

50,809
10
93
167

Thanks for your response. I actually implemented your linked solution number one in the past. I should mention, that there are other tables which are children of RealMaterial and VirtualMaterial vice versa. This led to a situation where you have to do a lot of programming for just inserting a new material and of course have a bunch of NULL entries. This is why i am looking for better solutions. I also thought about realizing my constraints at the application level but considered this solution always as bad practice. And yes i am also concerned with automatic insertion ;) – Dede1989 Apr 29 '14 at 20:27

score 0 · Answer 2 · answered Apr 29 '14 at 15:53

I don't really understand what all of your fields mean and you don't really give a lot of information, but you can possibly accomplish this with an trigger. Again, not lot of information here, so I'm not going to attempt to write a valid example, but a pseudo-code snippet would be:

IF (TG_OP = 'INSERT') THEN
    IF NEW.Parameter = 'REAL' THEN
        INSERT INTO RealMaterial (MatID) VALUES (NEW.MatID);
    ELSE
        INSERT INTO VirtualMaterial (MatID) VALUES (NEW.MatID);
    END IF;
END IF;

I have no idea of the relationship (if any) between Material.Parameter and Parameter2 and Parameter3. Assuming that Material.Parameter controls whether or not a material is real or virtual, then you will need to handle both INSERTS and UPDATES and be able to move the material from "RealMaterial" to "VirtualMaterial" and vice versa. You could probably handle deletes with your Foreign Key (cascade).

Two pieces of unsolicited advice:

When posting, always give as much (relevant) information as you can. It will lead to better answers. Here explaining the relationships would help. Also, you should always include information about what version of PostgreSQL you are using.
From my experience, it feels like you don't need different tables, but one table. I would strongly consider implementing this as a single table.

Propagate primary key to child tables

2 Answers2