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

Pujol Mathieu <mathieu.pujol@xxxxxxxxxxxxx> · Fri, 01 Aug 2014 09:18:44 +0200

Le 31/07/2014 20:38, Kynn Jones a écrit :
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.

Hi,
Maybe you can use inheritance.
CREATE TABLE super (
        super_id INT PRIMARY KEY,
        ...
        -- other columns
    );

CREATE TABLE sub_template (
        super_id INT PRIMARY KEY,
       FOREIGN KEY (super_id) REFERENCES super(super_id) UNIQUE,
    );

CREATE TABLE sub_1 (
        -- other columns
    ) INHERITS (sub_template);

CREATE TABLE sub_2 (
      -- other columns
    ) INHERITS (sub_template);

So the foreign key constraint will be on the sub_template avoiding two 
row of sub_x to reference the same foreign key.
This is just an idea I let you check for syntax. 
http://www.postgresql.org/docs/9.3/static/ddl-inherit.html
Regards,
Mathieu