Subject: multi-table unique index
From: Markus Bertheau <twanger@bluetwanger.de>
To: PGSQL-SQL <pgsql-sql@postgresql.org>
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Message-Id: <1056376700.2037.2.camel@fluor>
Mime-Version: 1.0
Date: 23 Jun 2003 15:58:20 +0200

-- 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