Postgres Pro
Downloads
Home   >   mailing lists

Thread: One sequence for different tables

One sequence for different tables

From
Robert.Farrugia@go.com.mt
Date:
Hi,

Is it possible to create one sequence to be used for more than one table ?
Let's say I create a pkey_seq sequence.
Can I use it in table A (pkey default nextval('pkey_seq') PRIMARY KEY) and
in table B as (pkey default nextval('pkey_seq') PRIMARY KEY) ?
Is it safe in that each table will have unique values i.e. if I have a
third table C which is used to link data both from A and B using the pkey
field, will there be records having the same pkey but from different
tables?

Thanks alot
Robert

Re: One sequence for different tables

From
Bruno Wolff III
Date:
On Mon, Mar 11, 2002 at 03:20:35PM +0100,
  Robert.Farrugia@go.com.mt wrote:
> Hi,
>
> Is it possible to create one sequence to be used for more than one table ?
> Let's say I create a pkey_seq sequence.
> Can I use it in table A (pkey default nextval('pkey_seq') PRIMARY KEY) and
> in table B as (pkey default nextval('pkey_seq') PRIMARY KEY) ?
> Is it safe in that each table will have unique values i.e. if I have a
> third table C which is used to link data both from A and B using the pkey
> field, will there be records having the same pkey but from different
> tables?

The sequence values will be unique accross tables when doing things this way.

Re: One sequence for different tables

From
Peter Eisentraut
Date:
Robert.Farrugia@go.com.mt writes:

> Is it possible to create one sequence to be used for more than one table ?

Yes.

> Let's say I create a pkey_seq sequence.
> Can I use it in table A (pkey default nextval('pkey_seq') PRIMARY KEY) and
> in table B as (pkey default nextval('pkey_seq') PRIMARY KEY) ?

Just like that.

> Is it safe in that each table will have unique values i.e. if I have a
> third table C which is used to link data both from A and B using the pkey
> field, will there be records having the same pkey but from different
> tables?

The sequence-generated numbers will be unique across A and B.  But I doubt
that that system is a good design.

--
Peter Eisentraut   peter_e@gmx.net

Re: One sequence for different tables

From
Stephan Szabo
Date:
On Mon, 11 Mar 2002 Robert.Farrugia@go.com.mt wrote:

> Is it possible to create one sequence to be used for more than one table ?
> Let's say I create a pkey_seq sequence.
> Can I use it in table A (pkey default nextval('pkey_seq') PRIMARY KEY) and
> in table B as (pkey default nextval('pkey_seq') PRIMARY KEY) ?

Yes.

> Is it safe in that each table will have unique values i.e. if I have a
> third table C which is used to link data both from A and B using the pkey
> field, will there be records having the same pkey but from different
> tables?

As long as you never insert an explicit value into the column and don't
have to roll over the sequence, you should be safe.

Re: One sequence for different tables

From
Brian McCane
Date:
On Mon, 11 Mar 2002, Stephan Szabo wrote:

You can add a trigger, BEFORE INSERT on each table to check the key in the
other table to prevent cross table duplications.  I have done this in the
past, but I no longer do.  It didn't work as well as I intended.  I also
occasionally got duplicates anyway, which I could never explain.  But that
was way back on a 6.? server I used to have.

- brian

> On Mon, 11 Mar 2002 Robert.Farrugia@go.com.mt wrote:
>
> > Is it possible to create one sequence to be used for more than one table ?
> > Let's say I create a pkey_seq sequence.
> > Can I use it in table A (pkey default nextval('pkey_seq') PRIMARY KEY) and
> > in table B as (pkey default nextval('pkey_seq') PRIMARY KEY) ?
>
> Yes.
>
> > Is it safe in that each table will have unique values i.e. if I have a
> > third table C which is used to link data both from A and B using the pkey
> > field, will there be records having the same pkey but from different
> > tables?
>
> As long as you never insert an explicit value into the column and don't
> have to roll over the sequence, you should be safe.
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>

Wm. Brian McCane                    | Life is full of doors that won't open
Search http://recall.maxbaud.net/   | when you knock, equally spaced amid those
Usenet http://freenews.maxbaud.net/ | that open when you don't want them to.
Auction http://www.sellit-here.com/ | - Roger Zelazny "Blood of Amber"