X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [64.117.224.193])
	by svr1.postgresql.org (Postfix) with ESMTP id 8E4EE30DE2B
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 23 Jun 2003 18:34:21 +0000 (GMT)
Received: from svr1.postgresql.org ([64.117.224.193])
	by localhost (svr1.postgresql.org [64.117.224.193]) (amavisd-new,
	port 10024) with ESMTP id 27359-05
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 23 Jun 2003 15:34:11 -0300 (ADT)
Received: from dbdevel.sunwave.net (h64-141-16-15.sunwave.net [64.141.16.15])
	by svr1.postgresql.org (Postfix) with ESMTP id 1686630F1CD
	for <pgsql-sql@postgresql.org>; Mon, 23 Jun 2003 15:34:11 -0300 (ADT)
Received: from localhost (localhost.localdomain [127.0.0.1])
	by dbdevel.sunwave.net (Postfix) with ESMTP
	id 97045178008; Mon, 23 Jun 2003 14:34:11 -0400 (EDT)
From: Michael A Nachbaur <mike@nachbaur.com>
To: Markus Bertheau <twanger@bluetwanger.de>
Subject: Re: multi-table unique index
Date: Mon, 23 Jun 2003 11:34:11 -0700
User-Agent: KMail/1.5
Cc: PGSQL-SQL <pgsql-sql@postgresql.org>
References: <1056376700.2037.2.camel@fluor>
	<200306231032.56257.mike@nachbaur.com>
	<1056392214.3405.0.camel@saphir>
In-Reply-To: <1056392214.3405.0.camel@saphir>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Message-Id: <200306231134.11430.mike@nachbaur.com>
X-Archive-Number: 200306/272
X-Sequence-Number: 14030

On Monday 23 June 2003 11:16 am, Markus Bertheau wrote:
> =D0=92 =D0=9F=D0=BD=D0=B4, 23.06.2003, =D0=B2 19:32, Michael A Nachbaur =
=D0=BF=D0=B8=D1=88=D0=B5=D1=82:
> > 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.
>
> Yeah, but I want to force this behaviour. so that it cannot happen by
> accident when you insert records without relying on the sequence.

I believe that's what I recommended.  IIRC the "serial" datatype is simply =
a=20
shortcut to what I listed above.  This way, if you do not explicitly specif=
y=20
an id for your record, it'll pull the default; which retrieves a new values=
=20
from the sequence.

If you want to ensure that a value is unique when a record is inserted, eve=
n=20
if the user explicitly specifies an ID values, you can create a unique=20
trigger on the tables, but this depends on what you want to do.