Re: How to implement a uniqueness constraint across multiple tables?

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