Thread: Sequences and inheritance

If I have a set of tables and I set their primary keys all to the same
sequence, as in

create sequence common_seq;

create table alpha (
    ID    integer primary key default nextval('common_seq),
    other_field text
);

create table beta (
    ID    integer primary key default nextval('common_seq),
    other_field real
);
create table gamma (
    ID    integer primary key default nextval('common_seq),
    other_field oid
);

Would this guarantee that each value of ID in any row of any of the
tables would be unique amongst all of them?

Would I get the same effect by creating a table like this:

create table common (
    ID     serial primary key
);

and then having alpha, beta and gamma inherit from it?

--

Bruce

On Tue, 31 Jul 2001, Bruce Richardson wrote:

> If I have a set of tables and I set their primary keys all to the same
> sequence, as in
>
> create sequence common_seq;
>
> create table alpha (
>     ID    integer primary key default nextval('common_seq),
>     other_field text
> );
>
> create table beta (
>     ID    integer primary key default nextval('common_seq),
>     other_field real
> );
> create table gamma (
>     ID    integer primary key default nextval('common_seq),
>     other_field oid
> );
>
> Would this guarantee that each value of ID in any row of any of the
> tables would be unique amongst all of them?
yes


> Would I get the same effect by creating a table like this:
>
> create table common (
>     ID     serial primary key
> );
>
> and then having alpha, beta and gamma inherit from it?
>
no. Primary key constraints are currently not inherited. See the todo-list
for more details.

- Einar Karttunen

Bruce Richardson <brichardson@lineone.net> writes:
> If I have a set of tables and I set their primary keys all to the same
> sequence, as in
> ...
> Would this guarantee that each value of ID in any row of any of the
> tables would be unique amongst all of them?

As long as the sequence doesn't wrap around, yes.

IIRC, you can set up a sequence to error out rather than wrap when
it hits the max...

            regards, tom lane

On Tue, Jul 31, 2001 at 04:31:42PM +0300, Einar Karttunen wrote:
> no. Primary key constraints are currently not inherited. See the todo-list
> for more details.

What about triggers?  Are they inherited or must they be applied to each
descendant?  I'd guess the latter - don't have the opportunity to test
it atm.

--
Bruce

You can fool some of the people all the time, you can fool all of
the people some of the time but you can never fool your mother.