Content-Type: text/plain;
  charset="utf-8"
From: Richard Huxton <dev@archonet.com>
Organization: Archonet Ltd
To: Markus Bertheau <twanger@bluetwanger.de>,
	PGSQL-SQL <pgsql-sql@postgresql.org>
Subject: Re: multi-table unique index
Date: Mon, 23 Jun 2003 20:40:17 +0100
User-Agent: KMail/1.4.3
References: <1056376700.2037.2.camel@fluor>
In-Reply-To: <1056376700.2037.2.camel@fluor>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200306232040.18121.dev@archonet.com>

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=20
foo/foo_1, then have a foreign key over both. The irritating thing is that=
=20
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_c=
ore=20
(foo_id, foo_type_id)
);


--=20
  Richard Huxton