I want to implement something akin to OO inheritance among DB tables. The idea is to define some "superclass" table, e.g.:
CREATE TABLE super (
super_id INT PRIMARY KEY,
...
-- other columns
);
CREATE TABLE sub_1 (
super_id INT PRIMARY KEY,
FOREIGN KEY (super_id) REFERENCES super(super_id),
...
-- other columns
);
CREATE TABLE sub_2 (
super_id INT PRIMARY KEY,
FOREIGN KEY (super_id) REFERENCES super(super_id),
...
-- other columns
);
...
CREATE TABLE sub_n (
super_id INT PRIMARY KEY,
FOREIGN KEY (super_id) REFERENCES super(super_id),
...
-- other columns
);
I cribbed this pattern from pp. 92-93 of Bill Kirwan's "SQL Antipatterns: Avoiding the pitfalls of database programming". The approach has a weakness, however, (which the author does not make sufficiently clear) and that is that, as presented above, it would be possible for multiple "sub" records (each from a different "sub_k" table) to refer to the same "super" record, and this may not be consistent with the semantics of some applications.
Does PostgreSQL have a good way to enforce the uniqueness of super_id values across multiple tables?
(BTW, one could use PostgreSQL built-in support for table inheritance to implement something very much like the scheme above. Unfortunately, as explained in the documentation, there's no built-in support yet for enforcing uniqueness across multiple subclass tables.)
Thanks in advance!
kj
PS: I'm sure that the problem described above crops up frequently, and that one could find much material about it on the Web, but my online searches have been hampered (I think) by my not having adequate search keywords for it. I'd be interested in learning keywords to facilitate researching this topic.
