From: Gary Stainburn <gary.stainburn@ringways.co.uk>
Organization: Ringways Garages Ltd
To: pgsql-sql@postgresql.org
Subject: foreign keys and lots of tables
Date: Wed, 18 May 2011 20:10:19 +0100
User-Agent: KMail/1.9.10
MIME-Version: 1.0
Content-Type: text/plain;
  charset="us-ascii"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <201105182010.19753.gary.stainburn@ringways.co.uk>

I have the following tables (individual seat allocation removed to make it 
simpler)

create table coaches (  -- carriages
  c_id          serial primary key,
  c_name        varchar(20) not null
);
create table trains ( -- one for each train
  t_id serial primary key
);
create table train_coaches ( -- which carriages are on what trains
  t_id		int4 not null references trains(t_id),
  c_id	int4 not null references coaches(c_id)
);

I now want to create bookings and allocate seats, but the seat must exist on 
the coach_seats table *AND* only for a carriage included in the train, i.e. 
an entry in train_coaches.

create table bookings (
  b_id	serial primary key,
  t_id		int4 not null references trains(t_id)
);

create table booking_seats (
  b_id	int4 not null references bookings(b_id),
  c_id	int4,		-- carriage ID
  c_seat	varchar(10) -- seat label
);

The following ensures the seat exists on the coach. (not shown)

alter table booking_seats add constraint seat_exists
    foreign key (c_id, c_seat) references coach_seats (c_id,c_seat);

How would I ensure that the coach exists on the train. I would need to convert 
the b_id to a t_id using the bookings table and I don't know how.

To complicate things, when the initial booking is made, bot c_id and c_seat 
are NULL. Will this make any difference?

Gary
-- 
Gary Stainburn
Group I.T. Manager
Ringways Garages
http://www.ringways.co.uk