From: Richard Huxton <dev@archonet.com>
To: Markus Bertheau <twanger@bluetwanger.de>
To: PGSQL-SQL <pgsql-sql@postgresql.org>
Subject: Re: multi-table unique index
Date: Mon, 23 Jun 2003 20:40:17 +0100
Message-ID: <200306232040.18121.dev@archonet.com> (raw)
In-Reply-To: <1056376700.2037.2.camel@fluor>
References: <1056376700.2037.2.camel@fluor>
On Monday 23 Jun 2003 2:58 pm, Markus Bertheau wrote:
> -- Suppose I have several types of foos
>
> create table foo_types (
> foo_type_id serial primary key,
> foo_name text not null
> );
>
> -- And the foos itself:
>
> create table foo (
> foo_id serial primary key,
> foo_type_id int not null references foo_types,
> foo_general_data1 text
> );
>
> -- 1st special foo:
>
> create table foo_1 (
> foo_1_id serial primary key,
> foo_id int not null references foo,
> foo_1_data1 int,
> foo_1_data2 text
> );
An alternative to Markus' ideas in the other thread - store the type in
foo/foo_1, then have a foreign key over both. The irritating thing is that
you're duplicating the type info unnecessarily.
CREATE TABLE foo (
foo_id serial unique,
foo_type_id int not null references foo_types,
...
PRIMARY KEY (foo_id,foo_type_id)
);
CREATE TABLE foo_1 (
extra_foo int4 not null,
extra_type int4 not null
extra1 text,
PRIMARY KEY (extra_foo, extra_type)
CONSTRAINT link_to_foo FOREIGN KEY (extra_foo,extra_type) REFERENCES foo_core
(foo_id, foo_type_id)
);
--
Richard Huxton
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dev@archonet.com, twanger@bluetwanger.de
Subject: Re: multi-table unique index
In-Reply-To: <200306232040.18121.dev@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox