Well that's easy: no.

Regards,
Marti

Wouldn't this be a problem only if new subn() could/would re-use an id?
if new sub() generates a unique id, there would be no chance of two subn
entries having the same id.

rjs

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

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

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 keeping multiple 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.