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

On 31 Jul 2014, at 20:38, Kynn Jones <kynnjo@gmail.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
tothe 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
keepingmultiple 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.