Re: multi-table unique index

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.

On Monday 23 June 2003 06:58 am, Markus Bertheau wrote:
> -- Suppose I have several types of foos
>
> create table foo_types (
> foo_type_id serial primary key,
> foo_name text not null
> );
>
> -- And the foos itself:
>
> create table foo (
> foo_id serial primary key,
> foo_type_id int not null references foo_types,
> foo_general_data1 text
> );
>
> -- 1st special foo:
>
> create table foo_1 (
> foo_1_id serial primary key,
> foo_id int not null references foo,
> foo_1_data1 int,
> foo_1_data2 text
> );
>
> -- 2nd special foo:
>
> create table foo_2 (
> foo_2_id serial primary key,
> foo_id int not null references foo,
> foo_2_data1 numeric(8,2),
> foo_2_data2 timestamp
> );
>
> -- And I have bars that each have zero or more (special) foos.
>
> create table bar (
> bar_id serial primary key
> );
>
> create table bar_foos (
> bar_id int not null references bar,
> foo_id int not null references foo
> );
>
> -- Now how do I prevent that I can insert a foo_1 and a foo_2 with
> -- the same foo_id? Or is the design errorneous itself?
>
> --
> Markus Bertheau
> Berlin, Berlin, Germany
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)

--
Michael A Nachbaur <mike(at)nachbaur(dot)com>