From: Gary Stainburn <gary.stainburn@ringways.co.uk>
To: pgsql-sql@postgresql.org
Subject: foreign keys and lots of tables
Date: Wed, 18 May 2011 20:10:19 +0100
Message-ID: <201105182010.19753.gary.stainburn@ringways.co.uk> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: gary.stainburn@ringways.co.uk
Subject: Re: foreign keys and lots of tables
In-Reply-To: <201105182010.19753.gary.stainburn@ringways.co.uk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox