Re: same sequence as primary key in more than one table

From: Shakespeare <>
Date: Mon, 15 Jun 2009 16:00:24 +0200
Message-ID: <4a3653f7$0$196$>

ddf schreef:
> On Jun 15, 6:02 am, Shakespeare <> wrote:
>> student4life schreef:
>>> Hello,
>>>  For example, I have 'sellers' and 'buyers' both need to authenticate
>>> from a database table 'users' with sequence as primary key which is
>>> also as primary key in tables 'sellers' and 'buyers' (both have one-to-
>>> one relationship with 'users') but I am not sure whether it's good
>>> practice to have sequence number jumping alternatively between tables.
>>> Could someone inform me what the best practice/design is under the
>>> scenario? TIA
>> As long as sequences and there values don't have a significant meaning,
>> there is no problem with using the same sequence for different tables.
>> It's a matter of taste and standards. Some standards require a separate
>> sequence for each table, some standards don't.
>> I wonder though: is the primary key in 'sellers' the same value as the
>> primary key in 'users', so if a seller has, let's say, 1676 as primary
>> key, is the primary key value in 'users' 1676 as well?
>> Shakespeare

> Presuming the columns are populated by triggers the same key value
> cannot be generated for two tables from the same sequence since the
> trigger must call sequence_name.nextval to consistently return a value
> successfully.
> David Fitzjarrell

That's exactly what I was wondering about... In some super/sub type implementations, the super type gets a key by sequence, and the sub types share this key.

The OP mentioned a 'users' table with a primary key populated by a sequence, but did not mention how the primary key of the sub type is populated. From what I read, it is the same *value*, not the same *sequence*, thus forming a one-to-one relationship.

Shakespeare Received on Mon Jun 15 2009 - 09:00:24 CDT

Original text of this message