Bartosz Dmytrak wrote:
> how about inheritance in postgres?
I know about Postgres' inheritance feature, but would prefer a more standard relational solution.
> With this approach all IDs will use the same sequence so there will not be duplicated PKs in inherited tables.
In my case, the primary keys are usually uuids, actually. It was just an example, anyway.
> This could be also modeled with "standard" SQL approach without redundant information. Solution depends on
requirements.
Well, it could be modeled in the way I described, if only I could use constant values in foreign keys:
FOREIGN KEY (id, 'derived2') REFERENCES base (id, type) ON DELETE CASCADE
I was quite surprised to find that this wasn't possible. Is there any good reason why not?
The reason I like this particular way of modeling the data is that I have a guarantee that there won't be an entry in
bothderived tables at the same time for the same row in the base table; also, I can have further constraints and
foreignkeys from and to the base table.
Of course, I could also omit the type field and simply live with the possibility of having two rows in the derived
tablesreferring to the same row of the base table. But it would be nice if I could rule that out with simple
constraints.
Regards,
--
Nils Gösche
Don't ask for whom the <Ctrl-G> tolls.