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

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