Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux