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

On Thu, Jul 31, 2014 at 3:19 PM, Rob Sargent <robjsargent(at)gmail(dot)com> wrote:

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

I'd thought that the ids of the sub_k tables were never generated
independently, but rather they must pre-exist as values of the super_id
column of the super table. After reading your post though, I'm no longer
sure that this is what Kirwan had in mind... (He does not give any details
at all on how the IDs should be created.)

If I understand you correctly, there should be a "business rule"
*somewhere* that says that entries in the super table must *always* be
created following these steps:

1. generate a new (unique) ID from a sequence super_seq;
2. insert a new entry in the super table having this (necessarily
unique) ID in its super_id column;
3. insert a new entry in some some sub_k table, having this ID in its
super_id column;
4. (somehow) disallow any subsequent updating of the super_id field of
this newly-added sub_k table (although it could still be OK to delete a
record from the super table, and cascade this to the appropriate record in
some sub_k table).

I'm sure this sort of thing could be implemented in PostgreSQL, though I'd
be hard-pressed to fill in the details. How much of this can be specified
in the definitions (CREATE TABLE ...) of the tables? For example, (1)
could be taken care of by defining the super_id column of the super table
as a SERIAL. I imagine that (2) and (3) would have to be encapsulated in a
"stored procedure" . Can (4) be implemented in the definitions of the
tables?

Thanks!