From: Michael A Nachbaur <mike@nachbaur.com>
To: Markus Bertheau <twanger@bluetwanger.de>,
	PGSQL-SQL <pgsql-sql@postgresql.org>
Subject: Re: multi-table unique index
Date: Mon, 23 Jun 2003 10:32:56 -0700
User-Agent: KMail/1.5
References: <1056376700.2037.2.camel@fluor>
In-Reply-To: <1056376700.2037.2.camel@fluor>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200306231032.56257.mike@nachbaur.com>

Instead of using the "serial" datatype, you can set it to "int4 PRIMARY KEY 
DEFAULT nextval(foo_type_id_seq)" and you can manually create the sequence 
"foo_type_id_seq".

This way all the tables share the same sequence.

On Monday 23 June 2003 06:58 am, 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
> );
>
> -- 2nd special foo:
>
> create table foo_2 (
>     foo_2_id serial primary key,
>     foo_id int not null references foo,
>     foo_2_data1 numeric(8,2),
>     foo_2_data2 timestamp
> );
>
> -- And I have bars that each have zero or more (special) foos.
>
> create table bar (
>     bar_id serial primary key
> );
>
> create table bar_foos (
>     bar_id int not null references bar,
>     foo_id int not null references foo
> );
>
> -- Now how do I prevent that I can insert a foo_1 and a foo_2 with
> -- the same foo_id? Or is the design errorneous itself?
>
> --
> Markus Bertheau
> Berlin, Berlin, Germany
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

-- 
Michael A Nachbaur <mike@nachbaur.com>