Thread: mutually exclusive subtypes

Let's say I have the following contrived schema:

TRANSPORTATION_MODE
mode_ID serial
model_number text

AIRPLANE
mode_ID int FK
operational_ceiling nt

AUTOMOBILE
mode_ID int FK
cruising range int


Of course, being an airplane and being an automobile are mutually exclusive.
How does one program the mutual exclusivity in PostgreSQL? I've studied using
check contraints, triggers, and rules, but I'm not finding any obvious way to
program this.

On Thu, April 13, 2006 11:08 am, Olinga K. Abbott said:

> Of course, being an airplane and being an automobile are mutually
> exclusive. How does one program the mutual exclusivity in PostgreSQL?
> I've studied using check contraints, triggers, and rules, but I'm not
> finding any obvious way to program this.

The obvious thing to me would be inheritance.  Both 'auto' and 'airplane'
are tables that inheret from the same table.  I'm not sure if 'unique'
holds across subtables, (I'm guessing it probably does, if it is in the
main table) but if it doesn't it would be fairly easy to write a procedure
that checks for the relevent entries in the top level table and all it's
children before entering data.

Daniel T. Staal

---------------------------------------------------------------
This email copyright the author.  Unless otherwise noted, you
are expressly allowed to retransmit, quote, or otherwise use
the contents for non-commercial purposes.  This copyright will
expire 5 years after the author's death, or in 30 years,
whichever is longer, unless such a period is in excess of
local copyright law.
---------------------------------------------------------------

The easiest way might be to use table inheritance.

--- "Olinga K. Abbott" <olingaa@yahoo.com> wrote:

> Let's say I have the following contrived schema:
>
> TRANSPORTATION_MODE
> mode_ID serial
> model_number text
>
> AIRPLANE
> mode_ID int FK
> operational_ceiling nt
>
> AUTOMOBILE
> mode_ID int FK
> cruising range int
>
>
> Of course, being an airplane and being an automobile are mutually exclusive.
> How does one program the mutual exclusivity in PostgreSQL? I've studied using
> check contraints, triggers, and rules, but I'm not finding any obvious way to
> program this.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that your
>        message can get through to the mailing list cleanly
>