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

On 31 Jul 2014, at 20:38, Kynn Jones <kynnjo(at)gmail(dot)com> 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?

Not in and of itself, but if you change the pattern a little you can have uniqueness:

CREATE TABLE super (
super_id INT,
— Add a type to the PK
type text,
PRIMARY KEY (super_id, type),
...
-- other columns
);

CREATE TABLE sub_1 (
super_id INT,
— Constrain the records in a sub-table to have a specific type
type text CHECK (type = ’sub_1’),
PRIMARY KEY (super_id, type),
FOREIGN KEY (super_id, type) REFERENCES super(super_id, type),
...
-- other columns
);

etc.

You still won’t have a unique super_id, but the combination of (super_id, type) will be unique.

Unfortunately, this approach breaks (again) if you would want to allow for multiple inheritance. You could fix that by keeping multiple levels of “type”, using multiple type-columns or perhaps an array, but that gets ugly fast.

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.