Обсуждение: One sequence for different tables
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
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.
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
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.
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"