From: Michael A Nachbaur <mike@nachbaur.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 10:32:56 -0700
Message-ID: <200306231032.56257.mike@nachbaur.com> (raw)
In-Reply-To: <1056376700.2037.2.camel@fluor>
References: <1056376700.2037.2.camel@fluor>
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>
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: mike@nachbaur.com, twanger@bluetwanger.de
Subject: Re: multi-table unique index
In-Reply-To: <200306231032.56257.mike@nachbaur.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