On 07/31/2014 12:38 PM, Kynn Jones wrote:
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.
Wouldn't this be a problem only if new subn() could/would re-use an id?
if new sub() generates a unique id, there would be no chance of two subn
entries having the same id.
rjs
