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

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
>    );
>
>
> 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?

This goes beyond the capabilities of declarative constraints to
enforce.  You can enforce it using triggers, but you need to handle
race conditions, which is not easy with MVCC behavior (where reads
don't block anything and writes don't block reads).  There are
basically two ways to cover that:

(1)  You can introduce blocking.  This can be done with LOCK TABLE
statements, but that tends to be a pretty crude tool for this.
You might be able to make clever use of transactional advisory
locks.  Or you could have a child_count column in the super table
which is maintained by "AFTER EACH ROW" triggers for INSERT and
DELETE.

(2)  You can ensure that all transactions which could affect this
invariant use the SERIALIZABLE transaction isolation level.  The
triggers can then check that there is not a matching row in more
than one "sub" table without worrying about the race conditions
(beyond automatically retrying a transaction which throws a
serialization failure).  An example of handling something vaguely
similar using SERIALIZABLE transactions is here:

http://wiki.postgresql.org/wiki/SSI#FK-Like_Constraints

You should probably review this entire chapter in the
documentation:

http://www.postgresql.org/docs/current/interactive/mvcc.html

By the way, I saw exactly this pattern in a financial accounting
system for courts. The super table had common information for all
financial transactions, and there were separate sub tables for
assessments, receipts, checks, etc.  We had a "transaction type"
code column in the super table to specify *which* of the sub tables
should be populated for each row in the super table.  I don't know
whether you have anything like that, but I think the issues are
similar either way -- perhaps a little simpler with such a code
than without.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company