On 31 Jul 2014, at 20:38, Kynn Jones <[email protected]> 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
> );
>
> 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?
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.
--
Sent via pgsql-general mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
